{"text_format":"1","workbook":"xlsx","n_sheets":12,"sheets":["EXO-1","EXO-2","REG","EXO-3","EXO-4","EXO-5","EXO-6","ATTRIB PERF","AT-PERF(2)","GIPS TWR","Attribution","AtributionPerf"],"not_rendered":{"charts":3,"images":6,"macros":0,"external_links":0,"defined_names":3,"comments":0,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"EXO-1","index":0,"n_rows":30,"n_cols":6,"rows_kept":30,"cut":false,"hidden":false,"merged":[]}
[null,"Exercice 1: Calcul des grands indices "]
[]
[null,"PARAMETRES"]
[null,"Taux sans Risque (% an)",null,null,0.04]
[null,"Market Portfolio (% an)"]
[null,"Expected Return, Market Porfolio",null,null,0.104]
[null,"Volatility, Market Porfolio",null,null,9.0800000000000006E-2]
[null,"Portfolio (% an)"]
[null,"Expected Return",null,null,0.14000000000000001]
[null,"Volatility,Portfolio",null,null,0.3]
[null,"Beta",null,null,1.2]
[null,"TE: Volat  (Portfolio  versus  Market Return)",null,null,0.03]
[]
[null,"CALCUL"]
[null,"PERFORMANCE MEASURE"]
[null,"Excess Return, Market (ERP)",null,null,{"$f":"E6-E4","v":6.4000000000000001E-2},{"$f":"_xlfn.FORMULATEXT(E16)","v":"=E6-E4"}]
[null,"Excess Return Portfolio",null,null,{"$f":"E9-E4","v":0.1},{"$f":"_xlfn.FORMULATEXT(E17)","v":"=E9-E4"}]
[null,"Treynor",null,null,{"$f":"E17/E11","v":8.3333333333333343E-2},"=E17/E11"]
[null,"Sharpe ",null,null,{"$f":"E17/E10","v":0.33333333333333337},"=E17/E10"]
[null,"Jensen Alpha",null,null,{"$f":"E17-(E16*E11)","v":2.3200000000000012E-2},"=E17-(E16*E11)"]
[null,"Information Ratio (IR)",null,null,{"$f":"E20/E12","v":0.77333333333333376},"=E20/E12"]
[]
[]
[null,"PERFORMANCE MEASURE"]
[null,"Excess Return, Market (ERP)"]
[null,"Excess Return Portfolio"]
[null,"Treynor"]
[null,"Sharpe "]
[null,"Jensen Alpha"]
[null,"Information Ratio (IR)"]

{"sheet":"EXO-2","index":1,"n_rows":71,"n_cols":14,"rows_kept":71,"cut":false,"hidden":false,"merged":[]}
[]
[null,"CALCUL DU  BETA TITRE VS MARKET"]
[null,"Données","Y","Y","X","X"]
[null,null,"Google","Yahoo! Inc.","Nasdaq","Nasdaq"]
[null,null,"Close","Performance","Close","Performance",null," 3 Methodes pour Calculer  Béta"]
[null,"2021-11-22",2941.57,{"$f":"C6/C7-1","v":-1.9166069255264206E-2},15854.76,{"$f":"E6/E7-1","v":-1.2622186350999876E-2}]
[null,"2021-11-19",2999.05,{"$f":"C7/C8-1","v":-5.0196073227211091E-3},16057.44,{"$f":"E7/E8-1","v":3.9846914818388868E-3}]
[null,"2021-11-18",3014.18,1.1049093665723042E-2,15993.71,4.5309602005330074E-3]
[null,"2021-11-17",2981.24,-9.3911830207460412E-5,15921.57,-3.2734730365735487E-3]
[null,"2021-11-16",2981.52,-2.0885211663588255E-3,15973.86,7.5697701189301192E-3]
[null,"2021-11-15",2987.76,-1.7207333331105135E-3,15853.85,-4.4827047038764611E-4]
[null,"2021-11-12",2992.91,1.9744732466541182E-2,15860.96,9.9768980176104805E-3,null,"Covar(Y,N)",{"$f":"COVAR(D6:D34,F6:F34)","v":5.0813759982675129E-5},{"$f":"COVAR(TABX,TABY)","v":5.0813759982675129E-5},{"$f":"_xlfn.FORMULATEXT(J12)","v":"=COVARIANCE(TABX;TABY)"}]
[null,"2021-11-11",2934.96,8.320488862820774E-4,15704.28,5.2212452257003328E-3,null,"Variance N",{"$f":"VARP(F6:F34)","v":5.2674763060092757E-5},{"$f":"VARP(TABX)","v":5.2674763060092757E-5},{"$f":"_xlfn.FORMULATEXT(J13)","v":"=VAR.P(TABX)"}]
[null,"2021-11-10",2932.52,-1.7571365876373868E-2,15622.71,-1.660714038424993E-2,null,"Ecartype",{"$f":"STDEVP(F6:F34)","v":7.2577381504221241E-3},{"$f":"SQRT(J13)","v":7.2577381504221241E-3},"=RACINE(J13)"]
[null,"2021-11-09",2984.97,-6.8964824591666307E-4,15886.54,-5.9953598842723554E-3,"M1","Beta",{"$f":"I12/I13","v":0.96466992978602395},{"$f":"J12/J13","v":0.96466992978602395},"=J12/J13"]
[null,"2021-11-08",2987.03,7.404131572423811E-4,15982.36,6.7432234361142207E-4]
[null,"2021-11-05",2984.82,3.7529509089808677E-3,15971.59,1.9623206825964967E-3,"M2","Pente",{"$f":"SLOPE(D6:D34,F6:F34)","v":0.96466992978602384},{"$f":"SLOPE(TABY,TABX)","v":0.96466992978602384},"=PENTE(TABY;TABX)"]
[null,"2021-11-04",2973.66,1.2895973840179709E-2,15940.31,8.1415013553356452E-3]
[null,"2021-11-03",2935.8,6.3552785833280723E-3,15811.58,1.0350424291994731E-2,"M3","Droite reg",{"$f":"LINEST(D6:D34,F6:F34,TRUE)","v":0.96466992978602362},{"$f":"LINEST(TABY,TABX,TRUE)","v":0.96466992978602362},"=DROITEREG(TABY;TABX;VRAI)"]
[null,"2021-11-02",2917.26,1.452974807684293E-2,15649.6,3.4419258370137484E-3]
[null,"2021-11-01",2875.48,-3.0326329242836514E-2,15595.92,6.2929117153458503E-3,null,"Donnée utilisateur d'analyse"]
[null,"2021-10-29",2965.41,1.4654859747209592E-2,15498.39,3.2541176531513116E-3]
[null,"2021-10-28",2922.58,-2.0385515015964861E-3,15448.12,1.3932937074687013E-2,null,"alpha",null,{"$f":"INTERCEPT(TABY,TABX)","v":-4.4036237006177346E-4},{"$f":"_xlfn.FORMULATEXT(J23)","v":"=ORDONNEE.ORIGINE(TABY;TABX)"}]
[null,"2021-10-27",2928.55,4.8366888137923114E-2,15235.84,8.5325856162832281E-6]
[null,"2021-10-26",2793.44,6.4782054145979817E-3,15235.71,5.910666191186742E-4,null,null,null,{"$f":"LINEST(TABY,TABX,TRUE)","v":0.96466992978602362},-4.4036237006177302E-4,{"$error":"#N/A"},{"$error":"#N/A"}]
[null,"2021-10-25",2775.46,1.0676284941388836E-3,15226.71,9.0462684391192649E-3,null,null,null,0.96466992978602362,-4.4036237006177302E-4,{"$error":"#N/A"},{"$error":"#N/A"}]
[null,"2021-10-22",2772.5,-2.9104114357352717E-2,15090.2,-8.2480595700493708E-3,null,null,null,0.96466992978602362,-4.4036237006177302E-4,{"$error":"#N/A"},{"$error":"#N/A"}]
[null,"2021-10-21",2855.61,2.5664431415231004E-3,15215.7,6.2175631279064447E-3,null,null,null,0.96466992978602362,-4.4036237006177302E-4,{"$error":"#N/A"},{"$error":"#N/A"}]
[null,"2021-10-20",2848.3,-9.782926117005708E-3,15121.68,-4.8978491105544641E-4]
[null,"2021-10-19",2876.44,6.0261400876466809E-3,15129.09,7.1416160902049164E-3]
[null,"2021-10-18",2859.21,9.073583906828997E-3,15021.81,8.355182871573108E-3]
[null,"2021-10-15",2833.5,1.8598138771817041E-3,14897.34,4.9860255015201638E-3]
[null,"2021-10-14",2828.24,2.5467730239303821E-2,14823.43,1.7279455160846835E-2]
[null,"2021-10-13",2758,8.6824223007320178E-3,14571.64,7.3082112993849702E-3]
[null,"2021-10-12",2734.26,null,14465.92]
[]
[]
[null,null,null,null,null,"Liste des calculs"]
[null,"Données","Y","X",null,"Les performances quotidiennes ( méthode au choix) pour chaque série"]
[null,null,"Yahoo! Inc.","Nasdaq"]
[null,null,"Close","Close",null,"La variance  du Nasdaq"]
[null,"2021-11-22",2941.57,15854.76]
[null,"2021-11-19",2999.05,16057.44,null,"L'écart type du Nasdaq"]
[null,"2021-11-18",3014.18,15993.71]
[null,"2021-11-17",2981.24,15921.57,null,"La covariance de Yahoo avec le Nasdaq "]
[null,"2021-11-16",2981.52,15973.86]
[null,"2021-11-15",2987.76,15853.85,null,"Le Beta de Yahoo avec la formule directe"]
[null,"2021-11-12",2992.91,15860.96]
[null,"2021-11-11",2934.96,15704.28,null,"Un graphique qui explicite la fonction de regression "]
[null,"2021-11-10",2932.52,15622.71]
[null,"2021-11-09",2984.97,15886.54,null,"RAPPORT DÉTAILLÉ"]
[null,"2021-11-08",2987.03,15982.36]
[null,"2021-11-05",2984.82,15971.59,null,"Statistiques de la régression"]
[null,"2021-11-04",2973.66,15940.31,null,"Coefficient de détermination multiple",0.45827706751325209]
[null,"2021-11-03",2935.8,15811.58,null,"Coefficient de détermination R^2",0.21001787060854579]
[null,"2021-11-02",2917.26,15649.6,null,"Coefficient de détermination R^2",0.1807592732236771]
[null,"2021-11-01",2875.48,15595.92,null,"Erreur-type",1.4072707515328329E-2]
[null,"2021-10-29",2965.41,15498.39,null,"Observations",29]
[null,"2021-10-28",2922.58,15448.12]
[null,"2021-10-27",2928.55,15235.84,null,"ANALYSE DE VARIANCE"]
[null,"2021-10-26",2793.44,15235.71,null,null,"Degré de liberté","Somme des carrés","Moyenne des carrés","F","Valeur critique de F"]
[null,"2021-10-25",2775.46,15226.71,null,"Régression",1,1.4215366819648814E-3,1.4215366819648814E-3,7.1779883309498036,1.2414673429639181E-2]
[null,"2021-10-22",2772.5,15090.2,null,"Résidus",27,5.3471096139234174E-3,1.9804109681197842E-4]
[null,"2021-10-21",2855.61,15215.7,null,"Total",28,6.7686462958882988E-3]
[null,"2021-10-20",2848.3,15121.68]
[null,"2021-10-19",2876.44,15129.09,null,null,"Coefficients","Erreur-type","Statistique t","Probabilité","Limite inférieure pour seuil de confiance = 95%","Limite supérieure pour seuil de confiance = 95%","Limite inférieure pour seuil de confiance =  95,0%","Limite supérieure pour seuil de confiance =  95,0%"]
[null,"2021-10-18",2859.21,15021.81,null,"Constante",-4.4036237006177302E-4,2.8548875281496793E-3,-0.15424858798104119,0.87856094342283786,-6.2981077214382571E-3,5.4173829813147101E-3,-6.2981077214382571E-3,5.4173829813147101E-3]
[null,"2021-10-15",2833.5,14897.34,null,"Variable X 1",0.96466992978602362,0.36006206437382171,2.6791768009875354,1.2414673429639218E-2,0.22588359827692706,1.7034562612951203,0.22588359827692706,1.7034562612951203]
[null,"2021-10-14",2828.24,14823.43]
[null,"2021-10-13",2758,14571.64]
[null,"2021-10-12",2734.26,14465.92]

{"sheet":"REG","index":2,"n_rows":18,"n_cols":9,"rows_kept":18,"cut":false,"hidden":false,"merged":[]}
["RAPPORT DÉTAILLÉ"]
[]
["Statistiques de la régression"]
["Coefficient de détermination multiple",0.45827706751325209]
["Coefficient de détermination R^2",0.21001787060854579]
["Coefficient de détermination R^2",0.1807592732236771]
["Erreur-type",1.4072707515328329E-2]
["Observations",29]
[]
["ANALYSE DE VARIANCE"]
[null,"Degré de liberté","Somme des carrés","Moyenne des carrés","F","Valeur critique de F"]
["Régression",1,1.4215366819648814E-3,1.4215366819648814E-3,7.1779883309498036,1.2414673429639181E-2]
["Résidus",27,5.3471096139234174E-3,1.9804109681197842E-4]
["Total",28,6.7686462958882988E-3]
[]
[null,"Coefficients","Erreur-type","Statistique t","Probabilité","Limite inférieure pour seuil de confiance = 95%","Limite supérieure pour seuil de confiance = 95%","Limite inférieure pour seuil de confiance =  95,0%","Limite supérieure pour seuil de confiance =  95,0%"]
["Constante",-4.4036237006177302E-4,2.8548875281496793E-3,-0.15424858798104119,0.87856094342283786,-6.2981077214382571E-3,5.4173829813147101E-3,-6.2981077214382571E-3,5.4173829813147101E-3]
["Variable X 1",0.96466992978602362,0.36006206437382171,2.6791768009875354,1.2414673429639218E-2,0.22588359827692706,1.7034562612951203,0.22588359827692706,1.7034562612951203]

{"sheet":"EXO-3","index":3,"n_rows":78,"n_cols":15,"rows_kept":78,"cut":false,"hidden":false,"merged":[]}
[]
[null,"CALCUL ALPHA GOOGLE",null,null,null,"Methode Arith",null,null,null,"Methode Ln"]
[null,"Données",null,null,"Y","Y",null,"Y","X","X","X"]
[null,null," Risk free","Day Risk free","Yahoo! Inc.","Yahoo! Inc.","Yahoo! Inc.","Yahoo! Inc.","Nasdaq","Nasdaq","Nasdaq"]
[null,null,null,null,"Close","Performance","(1 +ry )","Excess Ret","Close","Performance","Excess Ret","Diff perf",null,"CALCUL "]
[{"$f":"B6-B7","v":3},"2021-11-22",3.4000000000000002E-3,{"$f":"C6/360*A6","v":2.8333333333333335E-5},2941.57,{"$f":"E6/E7-1","v":-1.9166069255264206E-2},{"$f":"(1+F6)","v":0.98083393074473579},{"$f":"F6-D6","v":-1.9194402588597538E-2},15854.76,{"$f":"LN(I6/I7)","v":-1.2702522876091717E-2},{"$f":"J6-D6","v":-1.2730856209425051E-2},{"$f":"F6-J6","v":-6.4635463791724891E-3},null,"Nb. d'observation",{"$f":"COUNTA(H6:H34)","v":29}]
[1,"2021-11-19",3.4999999999999996E-3,{"$f":"C7/360*A7","v":9.722222222222221E-6},2999.05,{"$f":"E7/E8-1","v":-5.0196073227211091E-3},{"$f":"(1+F7)","v":0.99498039267727889},{"$f":"F7-D7","v":-5.0293295449433317E-3},16057.44,{"$f":"LN(I7/I8)","v":3.976773625243798E-3},{"$f":"J7-D7","v":3.9670514030215754E-3},{"$f":"F7-J7","v":-8.9963809479649062E-3},null,"Risk free rate jour",{"$f":"D37","v":1.1618773946360155E-5}]
[1,"2021-11-18",3.3E-3,9.1666666666666664E-6,3014.18,1.1049093665723042E-2,1.011049093665723,1.1039926999056376E-2,15993.71,{"$f":"LN(I8/I9)","v":4.5207263016453251E-3},4.5115596349786583E-3,{"$f":"F8-J8","v":6.528367364077717E-3},null,"Excess Ret jour Yahoo",{"$f":"H37","v":2.627766628931643E-3}]
[1,"2021-11-17",3.3E-3,9.1666666666666664E-6,2981.24,-9.3911830207460412E-5,0.99990608816979254,-1.0307849687412708E-4,15921.57,-3.2788425706529647E-3,{"$f":"J9-D9","v":-3.2880092373196316E-3},{"$f":"F9-J9","v":3.1849307404455043E-3},null,"Excess Ret jour Nasdaq",{"$f":"K37","v":3.1495616568126147E-3}]
[1,"2021-11-16",3.3E-3,9.1666666666666664E-6,2981.52,-2.0885211663588255E-3,0.99791147883364117,{"$f":"F10-D10","v":-2.0976878330254923E-3},15973.86,7.5412631793714841E-3,7.5320965127048172E-3,-9.6297843457303096E-3,null,"Active Return=",{"$f":"H37-K37","v":-5.2179502788097165E-4}]
[3,"2021-11-15",3.0000000000000001E-3,{"$f":"C11/360*A11","v":2.5000000000000001E-5},2987.76,-1.7207333331105135E-3,0.99827926666688949,-1.7457333331105135E-3,15853.85,-4.4837097363117001E-4,-4.7337097363117002E-4,-1.2723623594793434E-3,null,"ERT yahoo-ERT Nasdaq"]
[1,"2021-11-12",3.0000000000000001E-3,8.3333333333333337E-6,2992.91,1.9744732466541182E-2,1.0197447324665412,1.9736399133207849E-2,15860.96,9.9274573416807E-3,9.9191240083473659E-3,9.8172751248604816E-3,null,"Slope (Beta)",{"$f":"SLOPE(H6:H34,K6:K34)","v":0.96663564288281645}]
[1,"2021-11-11",3.0999999999999999E-3,8.6111111111111102E-6,2934.96,8.320488862820774E-4,1.0008320488862821,8.2343777517096626E-4,15704.28,5.2076617859780879E-3,5.1990506748669767E-3,-4.3756128996960105E-3,null,"Residual Return",{"$f":"H37-O12*K37","v":-4.1671192800048738E-4}]
[1,"2021-11-10",3.2000000000000002E-3,8.88888888888889E-6,2932.52,-1.7571365876373868E-2,0.98242863412362613,-1.7580254765262756E-2,15622.71,-1.6746584946104964E-2,-1.6755473834993852E-2,-8.2478093026890423E-4,null,"Alpha Ordonnée Orig",{"$f":"INTERCEPT(H6:H34,K6:K34)","v":-4.1671192800048738E-4}]
[1,"2021-11-09",3.2000000000000002E-3,8.88888888888889E-6,2984.97,-6.8964824591666307E-4,0.99931035175408334,-6.9853713480555197E-4,15886.54,-6.0134042119841837E-3,-6.0222931008730727E-3,5.3237559660675206E-3,null,"Résidu ",{"$f":"O10-O13","v":-1.0508309988048426E-4}]
[3,"2021-11-08",3.0999999999999999E-3,2.5833333333333329E-5,2987.03,7.404131572423811E-4,1.0007404131572424,7.1457982390904777E-4,15982.36,6.7409509045539035E-4,6.4826175712205702E-4,6.6318066786990757E-5,null,"représente l'écart Active Return - Alpha"]
[1,"2021-11-05",3.0999999999999999E-3,8.6111111111111102E-6,2984.82,3.7529509089808677E-3,1.0037529509089809,3.7443397978697565E-3,15971.59,1.9603978464356837E-3,1.9517867353245726E-3,1.792553062545184E-3,null,"Tracking Error",{"$f":"L40","v":7.4404117397637318E-2}]
[1,"2021-11-04",3.0999999999999999E-3,8.6111111111111102E-6,2973.66,1.2895973840179709E-2,1.0128959738401797,1.2887362729068598E-2,15940.31,8.1085381257672909E-3,8.0999270146561806E-3,4.7874357144124177E-3,null,"RI ",{"$f":"H43","v":7.6972246932402011E-2}]
[1,"2021-11-03",3.0999999999999999E-3,8.6111111111111102E-6,2935.8,6.3552785833280723E-3,1.0063552785833281,6.3466674722169611E-3,15811.58,1.0297225422838176E-2,1.0288614311727066E-2,-3.9419468395101038E-3]
[1,"2021-11-02",3.0999999999999999E-3,8.6111111111111102E-6,2917.26,1.452974807684293E-2,1.0145297480768429,1.452113696573182E-2,15649.6,3.4360159672863623E-3,3.4274048561752511E-3,1.1093732109556567E-2]
[3,"2021-11-01",2.8000000000000004E-3,2.3333333333333336E-5,2875.48,-3.0326329242836514E-2,0.96967367075716349,-3.0349662576169847E-2,15595.92,6.2731940243085085E-3,6.249860690975175E-3,-3.6599523267145023E-2]
[1,"2021-10-29",2.8999999999999998E-3,8.0555555555555556E-6,2965.41,1.4654859747209592E-2,1.0146548597472096,1.4646804191654036E-2,15498.39,3.2488344705967676E-3,3.2407789150412122E-3,1.1406025276612825E-2]
[1,"2021-10-28",2.8000000000000004E-3,7.7777777777777792E-6,2922.58,-2.0385515015964861E-3,0.99796144849840351,-2.0463292793742641E-3,15448.12,1.3836765974631106E-2,1.3828988196853327E-2,-1.5875317476227592E-2]
[1,"2021-10-27",2.8999999999999998E-3,8.0555555555555556E-6,2928.55,4.8366888137923114E-2,1.0483668881379231,4.8358832582367561E-2,15235.84,8.5325492139816482E-6,4.7699365842609264E-7,4.8358355588709136E-2]
[1,"2021-10-26",2.8999999999999998E-3,8.0555555555555556E-6,2793.44,6.4782054145979817E-3,1.006478205414598,6.4701498590424263E-3,15235.71,5.9089200804568573E-4,5.8283645249013021E-4,5.8873134065522961E-3]
[3,"2021-10-25",2.8000000000000004E-3,2.3333333333333336E-5,2775.46,1.0676284941388836E-3,1.0010676284941389,1.0442951608055503E-3,15226.71,9.0055960576116423E-3,8.9822627242783087E-3,-7.9379675634727587E-3]
[1,"2021-10-22",2.5999999999999999E-3,7.2222222222222221E-6,2772.5,-2.9104114357352717E-2,0.97089588564264728,-2.9111336579574938E-2,15090.2,-8.2822630179447358E-3,-8.2894852401669585E-3,-2.0821851339407983E-2]
[1,"2021-10-21",2.8000000000000004E-3,7.7777777777777792E-6,2855.61,2.5664431415231004E-3,1.0025664431415231,2.5586653637453224E-3,15215.7,6.1983138302260593E-3,6.1905360524482818E-3,-3.631870688702959E-3]
[1,"2021-10-20",2.7000000000000001E-3,7.5000000000000002E-6,2848.3,-9.782926117005708E-3,0.99021707388299429,-9.7904261170057086E-3,15121.68,-4.8990489486410067E-4,-4.9740489486410069E-4,-9.2930212221416075E-3]
[1,"2021-10-19",2.8999999999999998E-3,8.0555555555555556E-6,2876.44,6.0261400876466809E-3,1.0060261400876467,6.0180845320911254E-3,15129.09,7.1162355172443917E-3,7.1081799616888363E-3,-1.0900954295977108E-3]
[3,"2021-10-18",2.5000000000000001E-3,2.0833333333333336E-5,2859.21,9.073583906828997E-3,1.009073583906829,9.0527505734956642E-3,15021.81,8.3204715434700722E-3,8.2996382101367394E-3,7.5311236335892477E-4]
[1,"2021-10-15",2.5000000000000001E-3,6.9444444444444448E-6,2833.5,1.8598138771817041E-3,1.0018598138771817,1.8528694327372597E-3,14897.34,4.9736364407522542E-3,4.9666919963078093E-3,-3.1138225635705501E-3]
[1,"2021-10-14",2.5000000000000001E-3,6.9444444444444448E-6,2828.24,2.5467730239303821E-2,1.0254677302393038,2.5460785794859376E-2,14823.43,1.7131863155900207E-2,1.7124918711455762E-2,8.3358670834036142E-3]
[{"$f":"B34-B35","v":1},"2021-10-13",2.7000000000000001E-3,7.5000000000000002E-6,2758,8.6824223007320178E-3,1.008682422300732,8.6749223007320173E-3,14571.64,7.2816357245811345E-3,7.2741357245811348E-3,1.4007865761508833E-3]
[null,"2021-10-12",2.1800000000000001E-3,null,2734.26,null,null,null,14465.92]
[null,null,null,null,null,{"$f":"SQRT(SUM(((F6:F34)*(F6:F34>0)-F37)^2)/28)","v":1.1295007689119224E-2},{"$f":"_xlfn.FORMULATEXT(F36)","v":"{=RACINE(SOMME(((F6:F34)*(F6:F34>0)-F37)^2)/28)}"}]
[null,"Moyenne jour",{"$f":"AVERAGE(C6:C34)","v":2.9689655172413787E-3},{"$f":"AVERAGE(D6:D34)","v":1.1618773946360155E-5},null,{"$f":"AVERAGE(F6:F34)","v":2.6393854028780028E-3},null,{"$f":"AVERAGE(H6:H34)","v":2.627766628931643E-3},null,{"$f":"AVERAGE(J6:J34)","v":3.1611804307589749E-3},{"$f":"AVERAGE(K6:K34)","v":3.1495616568126147E-3},{"$f":"AVERAGE(L6:L34)","v":-5.2179502788097208E-4}]
[null,"Perform période",null,null,"",{"$f":"PRODUCT(G6:G34)-1","v":7.5819417319493709E-2},null,{"$f":"PRODUCT(1+H6:H34)-1","v":7.5456780169620652E-2},null,{"$f":"SUM(J6:J34)","v":9.1674232492010274E-2},{"$f":"SUM(K6:K34)","v":9.1337288047565829E-2},{"$f":"SUM(L6:L34)","v":-1.513205580854819E-2}]
[null,"Volatilité jour",{"$f":"STDEV(C6:C34)","v":2.7268957792942876E-4},{"$f":"STDEV(D6:D34)","v":6.7867284793134931E-6},"",{"$f":"STDEV(F6:F34)","v":1.5547905573108436E-2},null,{"$f":"STDEV(H6:H34)","v":1.555020627921235E-2},null,{"$f":"STDEV(J6:J34)","v":7.3848247038551243E-3},{"$f":"STDEV(K6:K34)","v":7.3860406746868671E-3},{"$f":"STDEV(L6:L34)","v":1.381649774191933E-2}]
[null,"Volatilité periode",{"$f":"C39*SQRT(29)","v":1.4684783183379214E-3},{"$f":"D39*SQRT(29)","v":3.6547651362376492E-5},null,{"$f":"F39*SQRT(COUNTA(F6:F34))","v":8.3728033916953964E-2},null,{"$f":"H39*SQRT(COUNTA(H6:H34))","v":8.3740423598496316E-2},null,{"$f":"J39*SQRT(29)","v":3.9768498102058097E-2},{"$f":"K39*SQRT(29)","v":3.9775046305387703E-2},{"$f":"L39*SQRT(29)","v":7.4404117397637318E-2}]
[null,"Sharpe période",null,null,null,null,null,{"$f":"H38/H40","v":0.9010795136576738},null,null,{"$f":"K38/K40","v":2.2963464918755809}]
[null,"Treynor période",null,null,null,null,null,{"$f":"H38/$O$12","v":7.8061243370443512E-2}]
[null,"Info Ratio période",null,null,null,null,null,{"$f":"L40/$O$12","v":7.6972246932402011E-2}]
[null,null,null,null,"verif perf",{"$f":"(E6/E35-1)-F38","v":4.4408920985006262E-16},null,null,"verif perf",{"$f":"LN(I6/I35)-J38","v":1.2490009027033011E-16}]
[]
[null,"Données",null,"Y","X"]
[null,null," Risk free","Yahoo! Inc.","Nasdaq"]
[null,null,null,"Close","Close"]
[null,"2016-09-13",3.4000000000000002E-3,43.04,5155.25]
[null,"2016-09-12",3.4999999999999996E-3,43.46,5211.8900000000003]
[null,"2016-09-09",3.3E-3,42.92,5125.91]
[null,"2016-09-08",3.3E-3,44.36,5259.48]
[null,"2016-09-07",3.3E-3,44.35,5283.93]
[null,"2016-09-06",3.0000000000000001E-3,44.71,5275.91]
[null,"2016-09-02",3.0000000000000001E-3,43.28,5249.9]
[null,"2016-09-01",3.0999999999999999E-3,42.93,5227.21]
[null,"2016-08-31",3.2000000000000002E-3,42.75,5213.22]
[null,"2016-08-30",3.2000000000000002E-3,42.58,5222.99]
[null,"2016-08-29",3.0999999999999999E-3,42.26,5232.33]
[null,"2016-08-26",3.0999999999999999E-3,42.27,5218.92]
[null,"2016-08-25",3.0999999999999999E-3,42.03,5212.2]
[null,"2016-08-24",3.0999999999999999E-3,41.91,5217.6899999999996]
[null,"2016-08-23",3.0999999999999999E-3,42.6,5260.08]
[null,"2016-08-22",2.8000000000000004E-3,42.52,5244.6]
[null,"2016-08-19",2.8999999999999998E-3,43.02,5238.38]
[null,"2016-08-18",2.8000000000000004E-3,42.9,5240.1499999999996]
[null,"2016-08-17",2.8999999999999998E-3,42.7,5228.66]
[null,"2016-08-16",2.8999999999999998E-3,42.49,5227.1099999999997]
[null,"2016-08-15",2.8000000000000004E-3,42.67,5262.02]
[null,"2016-08-12",2.5999999999999999E-3,42.94,5232.8900000000003]
[null,"2016-08-11",2.8000000000000004E-3,41.27,5228.3999999999996]
[null,"2016-08-10",2.7000000000000001E-3,39.93,5204.58]
[null,"2016-08-09",2.8999999999999998E-3,39.24,5225.4799999999996]
[null,"2016-08-08",2.5000000000000001E-3,39.24,5213.1400000000003]
[null,"2016-08-05",2.5000000000000001E-3,38.99,5221.12]
[null,"2016-08-04",2.5000000000000001E-3,38.92,5166.25]
[null,"2016-08-03",2.7000000000000001E-3,38.39,5159.74]
[null,"2016-08-02",2.1800000000000001E-3,38.57,5137.7299999999996]

{"sheet":"EXO-4","index":4,"n_rows":80,"n_cols":14,"rows_kept":80,"cut":false,"hidden":false,"merged":[]}
[null,"NORMALISATION DES VARIABLES - SKEWNESS - KURTOSIS - CORNISH FISHER "]
[]
[null,"Tableau Rendement Actif A et B"]
[]
[null,"Année","A","B","M",null,"Hypothèses"]
[null,1,7.0000000000000007E-2,0.15,0.115,null,"Un investisseur veut une valeur minimale de rendement ( valeur de réserve de 9%)",null,null,null,null,null,null,0.115]
[null,2,0.19,0.25,0.185,null,"Le risque de taux ( annuel ) sans risque est de 5%  ",null,null,null,null,null,null,0.185]
[null,3,0.21,0.35,0.495,null,"RL",0.09,null,null,null,null,null,0.495]
[null,4,0.13,0.17,0.19500000000000001,null,"RF",0.05,null,null,null,null,null,0.19500000000000001]
[null,5,0.22,0.18,0.185,null,null,null,null,null,null,null,null,0.185]
[]
[]
[]
[]
[null,"Question 1","Entre A et B quel Actif  a d'avantage de risque en termes de mouvement extrêmes"]
[null,null,"Baser votre analyse à partir de la distribution des rendements des fonds;  grace notamment au Kurtosis et au Skewness"]
[]
[null,"Question 2","Calculer la perte maximale qu'il soit possible avec une probabilié de 95% pour le fonds A et le fonds B en utilisant l'approximation de Cornish-Fisher"]
[]
[]
[null,"Solution Question 2"]
[]
[null,"Le Skewness( u3 ou coefficient d'asymétrie)   permet de juger l'occurrence de mouvements négatifs"]
[null,"Si Skewness  négatif => Plus grande proba de pertes par rapport aux gains . Si skew = 0  la distribution est normale"]
[]
[null,"Le kurtosis (u4 ou coefficient d'applatissement)  permet de juger l'occurrence de mouvements extrêmes"]
[null,"Si Kurtosis < 0 Forte probabilité de fort mouvement "]
[]
[]
[]
[null,"Calcul indicateurs statistique",null,null,"A","B",null,"Année","A","Return-u","(Return-u)^2","(Return-u)^3","(Return-u)^4"]
[null,"Rendement moyen ",null,null,{"$f":"AVERAGE(C6:C10)","v":0.16399999999999998},{"$f":"AVERAGE(D6:D10)","v":0.22000000000000003},{"$f":"_xlfn.FORMULATEXT(F32)","v":"=MOYENNE(D6:D10)"},1,{"$f":"C6","v":7.0000000000000007E-2},{"$f":"I32-$E$32","v":-9.3999999999999972E-2},{"$f":"J32^2","v":8.8359999999999949E-3},{"$f":"J32^3","v":-8.3058399999999923E-4},{"$f":"J32^4","v":7.8074895999999915E-5}]
[null,"Variance",null,null,{"$f":"K37","v":3.1840000000000002E-3},{"$f":"K45","v":5.3599999999999993E-3},"=K45",2,{"$f":"C7","v":0.19},{"$f":"I33-$E$32","v":2.6000000000000023E-2},{"$f":"J33^2","v":6.7600000000000125E-4},{"$f":"J33^3","v":1.7576000000000047E-5},{"$f":"J33^4","v":4.569760000000017E-7}]
[null,"Volatilité",null,null,{"$f":"SQRT(K37)","v":5.6426943918663541E-2},{"$f":"SQRT(K45)","v":7.32120208708925E-2},"=RACINE(K45)",3,{"$f":"C8","v":0.21},{"$f":"I34-$E$32","v":4.6000000000000013E-2},2.1160000000000011E-3,{"$f":"J34^3","v":9.7336000000000086E-5},{"$f":"J34^4","v":4.4774560000000046E-6}]
[null,"Volat Excel",null,null,{"$f":"STDEVP(C6:C10)","v":5.6426943918663541E-2},{"$f":"STDEVP(D6:D10)","v":7.3212020870892416E-2},"=ECARTYPEP(D6:D10)",4,{"$f":"C9","v":0.13},-3.3999999999999975E-2,1.1559999999999982E-3,{"$f":"J35^3","v":-3.9303999999999907E-5},{"$f":"J35^4","v":1.3363359999999957E-6}]
[null,"Skewnes U3",null,"S",{"$f":"L37/E35^3","v":-0.64493940787316051},{"$f":"L45/(F35^3)","v":0.86234920996846665},"=L45/(F35^3)",5,{"$f":"C10","v":0.22},5.6000000000000022E-2,3.1360000000000025E-3,{"$f":"J36^3","v":1.7561600000000021E-4},{"$f":"J36^4","v":9.8344960000000164E-6}]
[null,"Kurtosis U4",null,"K",{"$f":"M37/E35^4-3","v":-1.1420103027701338},{"$f":"M45/F35^4-3","v":-0.77762307863665869},"=M45/F35^4-3","Total",null,{"$f":"SUM(J32:J36)/5","v":2.2204460492503132E-17},{"$f":"SUM(K32:K36)/5","v":3.1840000000000002E-3},{"$f":"SUM(L32:L36)/5","v":-1.1587199999999976E-4},{"$f":"SUM(M32:M36)/5","v":1.8836031999999985E-5}]
[null,null,null,null,{"$f":"_xlfn.SKEW.P(I32:I36)","v":-0.6449394078731604},{"$f":"_xlfn.SKEW.P(I40:I44)","v":0.86234920996846376},"=COEFFICIENT.ASYMETRIE.P(I40:I44)",null," ","M1","M2","M3","M4"]
[null,"RL",null,{"$f":"STDEV(I32:I36)","v":6.3087241182349998E-2},{"$f":"KURT(I32:I36)","v":-0.56804121108053174},{"$f":"KURT(I40:I44)","v":0.88950768545332259},"=KURTOSIS(I40:I44)","Année","B","Return-u","(Return-u)^2","(Return-u)^3","(Return-u)^4"]
[null,"RF",null,{"$f":"5*6/4/3/2","v":1.25},{"$f":"SUM(M32:M36)/D39^4","v":5.9455670311355808},null,null,1,{"$f":"D6","v":0.15},{"$f":"I40-$F$32","v":-7.0000000000000034E-2},{"$f":"J40^2","v":4.900000000000005E-3},{"$f":"J40^3","v":-3.4300000000000053E-4},{"$f":"J40^4","v":2.401000000000005E-5}]
[null,null,null,{"$f":"3*4*4/3/2","v":8},{"$f":"E40*D40-D41","v":-0.56804121108052374},null,null,2,{"$f":"D7","v":0.25},{"$f":"I41-$F$32","v":2.9999999999999971E-2},{"$f":"J41^2","v":8.9999999999999824E-4},{"$f":"J41^3","v":2.6999999999999921E-5},{"$f":"J41^4","v":8.099999999999968E-7}]
[null,"le skew negatif veut dire que le portefeuille est risqué à la baisse",null,null,null,null,null,3,{"$f":"D8","v":0.35},0.12999999999999995,1.6899999999999988E-2,{"$f":"J42^3","v":2.1969999999999976E-3},{"$f":"J42^4","v":2.8560999999999962E-4}]
[null,"Le kurtosis élevé renforce le fait que les coups dur de grandes",null,null,null,null,null,4,{"$f":"D9","v":0.17},-5.0000000000000017E-2,2.5000000000000018E-3,{"$f":"J43^3","v":-1.2500000000000014E-4},{"$f":"J43^4","v":6.2500000000000088E-6}]
[null,"amplituel peuvent se produire a la baisse",null,null,null,null,null,5,{"$f":"D10","v":0.18},-4.0000000000000036E-2,1.6000000000000029E-3,{"$f":"J44^3","v":-6.4000000000000173E-5},{"$f":"J44^4","v":2.5600000000000094E-6}]
[null,null,null,null,null,null,null,"Total",null,{"$f":"SUM(J40:J44)/5","v":-3.3306690738754695E-17},{"$f":"SUM(K40:K44)/5","v":5.3599999999999993E-3},{"$f":"SUM(L40:L44)/5","v":3.3839999999999928E-4},{"$f":"SUM(M40:M44)/5","v":6.3847999999999948E-5}]
[null,"Le fond A est le plus dangereux car il perd plus souvent et en plus grande quantité",null,null,null,null,null,null,null,"M1","M2","M3","M4"]
[]
[null,"Lorsque la distribution est symétrique, le coefficient de Skewness est nul."]
[null,"Lorsque la distribution possède une forte queue vers la droite, le coefficient cient de Skewness est positif (les + l'emportent). "]
[null,"Lorsque la distribution possède une forte queue vers la gauche,le coeffcient de Skewness est négatif (les - l'emportent). "]
[]
[null,"Solution Question 2"]
[]
[null,"Pour répondre à cette question il faut répondre à la notion de Value at Risk (Var) qui se concentre sur l’évaluation du risque de perte maximale. "]
[null,"On recourt  au calcul de VaR modifiée par l’approximation de Cornish- Fisher "]
[null,"Et ceci afin de prendre en compte le caractère « non  Normale » de la distribution des performance de A et de B mis en évidence à la question 1,"]
[null," "]
[null,"Formule de calcul de la Valeur au risque modifiée"]
[null,"MVvaRα = RL – E(R) +z(1-α)*σ(R)  "]
[]
[null,"Tout d'abord calculons le terme z(1-α) qui est donné par la formule suivante  avec z(1-α) = zc= 1,645 aus seuil de confiance de 5%"]
[null,"Correspond à la probabilité 95% que X"]
[]
[]
[]
[null,"Cacul décomposé de Z pour A",null,null,null,"Cacul décomposé de Z pour B"]
[null,"z(c) =",{"$f":"_xlfn.NORM.INV(95%,0,1)","v":1.6448536269514715},null,"z(c) =",{"$f":"_xlfn.NORM.INV(95%,0,1)","v":1.6448536269514715}]
[null,"+",{"$f":"E36/6*(C67^2-1)","v":-0.18332869756437317},null,"+",{"$f":"F36/6*(F67^2-1)","v":0.24512900836767784}]
[null,"+",{"$f":"E37/24*(C67^3-3*C67)","v":2.3046620492480251E-2},null,"+",{"$f":"F37/24*(F67^3-3*F67)","v":1.5693014271466247E-2}]
[null,"-",{"$f":"E36^2/36*(2*C67^3-5*C67)","v":7.8126115476385132E-3},null,"-",{"$f":"F36^2/36*(2*F67^3-5*F67)","v":1.3967694955578775E-2}]
[null,"=",{"$f":"C67+C68+C69-C70","v":1.4767589383319402},null,"=",{"$f":"F67+F68+F69-F70","v":1.8917079546350368}]
[]
[null,"Application formule Valeur au Risque modifié"]
[null,"Var a 95",null,null,null,"Var b 95"]
[null,"MVAR A =","RL",{"$f":"H8","v":0.09},null,"MVAR B =","RL",{"$f":"H8","v":0.09}]
[null,"-","E(R) ",{"$f":"E32","v":0.16399999999999998},null,"-","E(R) ",{"$f":"F32","v":0.22000000000000003}]
[null,"+","zc * σ",{"$f":"C71*E34","v":8.3328993794641495E-2},null,"+","zc * σ",{"$f":"F71*F34","v":0.13849576225637367}]
[null,null,null,{"$f":"D75-D76+D77","v":9.328993794641513E-3},null,null,null,{"$f":"H75-H76+H77","v":8.4957622563736346E-3}]
[]
[null,null,null,{"$f":"D75-D76+C67*E34","v":1.8814063362401018E-2},null,null,null,{"$f":"H75-H76+F35*F67","v":-9.5769419340658163E-3}]

{"sheet":"EXO-5","index":5,"n_rows":81,"n_cols":9,"rows_kept":81,"cut":false,"hidden":false,"merged":[]}
[]
[]
[null,"CALCUL INDICE SORTINO"]
[]
[null,"Hypothèses"]
[null,"Vous décidez d’investir à horizon 6 ans sur un fond de gestion active sur les nouvelles technologies."]
[null,"Votre valeur minimale de rendement correspond la performance du benchmark du secteur."]
[null,"Vous envisager d’investir 100,000 dans le Fonds TEC dont les performances annuelles sont synthétisées dans  le tableau  "]
[]
[]
[null,"Tableau Rendement Actif A et B"]
[null,"Montant investi Début A1",null,100000]
[null,"Flux de trésorie attendu sur le benchmark (gestion passive)",null,null,null,null,"Rendement attendus sur le fond TECF (gestion active)"]
[null,"Année","Benchmark",null,null,null,"Année","Fond TEC"]
[null,1,10000,null,null,null,1,-0.18459999999999999]
[null,2,11000,null,null,null,2,0.70589999999999997]
[null,3,12000,null,null,null,3,-0.20419999999999999]
[null,4,13500,null,null,null,4,7.0699999999999999E-2]
[null,5,15000,null,null,null,5,-5.96E-2]
[null,6,20000,null,null,null,6,0.59340000000000004]
[]
[null,"Question 1","Quel est le rendement du portefeuille benchmark ( utiliser la fonction Tri)"]
[null,null,"Calculer le MAR  (minimum acceptable return)  "]
[]
[null,"Question 2","Quel est le rendement du fonds TEC ( utiliser la fonction Tri)"]
[]
[null,"Question 3","Calculer l'indice Sortino du Fonds avec don MAR (minimum acceptable return) "]
[]
[]
[null,"Reponse Q1: "]
[]
[null,"L’investisseur souhaite à minima sur les 6 ans d’investissement un taux de rendement (rentabilité) au moins égale à celui benchmark."]
[null,"Il faut donc calculer le taux de rendement du benchmark à partir des les flux de trésorerie attendus par les analystes sur le benchmark."]
[]
[null,"Etape 1 reconstitution des flux de trésorerie",null,null,null,null,"Etape 2 : Calcul du taux de rendement du Benchmark à partir des flux de trésorerie prévus"]
[null,null,null,null,null,null,null,"Ce taux correspond au MAR"]
[null,"Année","Investissement","Flux Bench","Flux Net ",null,"Total","Flux Actualisés","Rend  TRI"]
[null,0,{"$f":"-D12","v":-100000},0,{"$f":"C38+D38","v":-100000},null,{"$f":"D38-C38","v":100000},{"$f":"E38/(1+$I$38)^B38","v":-100000},{"$f":"IRR(E38:E44)","v":0.12961676850112425}]
[null,1,null,{"$f":"C15","v":10000},10000,null,{"$f":"G38+D39","v":110000},8852.5598050999961]
[null,2,null,11000,11000,null,121000,8620.4596613159283]
[null,3,null,12000,12000,null,133000,8325.0692399183263]
[null,4,null,13500,13500,null,146500,8291.0444993972269]
[null,5,null,15000,15000,null,161500,8155.2185864065887]
[null,6,{"$f":"D12","v":100000},20000,120000,null,181500,57755.648207861901]
[null,"Total",{"$f":"SUM(C38:C44)","v":0},{"$f":"SUM(D38:D44)","v":81500},{"$f":"SUM(E38:E44)","v":81500},null,null,{"$f":"SUM(H38:H44)","v":0}]
[null,null,null,null,null,"Le taux de rendement est le taux d'actualisation des flux dans le temps"]
[null,null,null,null,null,"La somme des montants actualisés dans le temps  au taux de rendement doit être égale à zéro"]
[]
[null,"Reponse Q2: "]
[null,"Performance fond TEC"]
[null,"Année","Fond TEC"]
[null,1,{"$f":"H15","v":-0.18459999999999999}]
[null,2,0.70589999999999997,null,null,"MAR (RL)",{"$f":"I38","v":0.12961676850112425}]
[null,3,-0.20419999999999999,null,null,"Rtec - RL",{"$f":"C58-I38","v":2.398323149887574E-2}]
[null,4,7.0699999999999999E-2]
[null,5,-5.96E-2,null,null,{"$f":"CONCATENATE(\"L'investisseur doit donc recherché un rendement minimum de son investissement de \", TEXT(G53*100,\" ##,###\"),\"%\" )","v":"L'investisseur doit donc recherché un rendement minimum de son investissement de  12,962%"}]
[null,6,0.59340000000000004,null,null,{"$f":"CONCATENATE(\"L'excess Return du fond es de \", TEXT(G54*100,\" ##,###\"),\"%\" )","v":"L'excess Return du fond es de  2,398%"}]
[null,"Total",{"$f":"SUM(C52:C57)/COUNTA(B52:B57)","v":0.15359999999999999},{"$f":"AVERAGE(C52:C57)","v":0.15359999999999999}]
[]
[]
[]
[null,"Réponse Q3: ","Calcul de l'indice de Sortino pour juger la qualité de l'investissement"]
[]
[]
[null,"Etape 1 : Calcul de la Semi-Variance",null,null,null,null,"Etape 2 : Appliquer la formule de Sortino"]
[]
[null,"SV = 1/T *   Σ ( Rteci-RL)^2 , si Rpt>Rl",null,null,null,null,"Appliquer la formule de Sortino"]
[null,"Année i","Fond TEC","RL","( Rpt-Rl)^2",null,"RS=  Rtec-RL   /  Racine ( SV  /in )"]
[null,1,{"$f":"C52","v":-0.18459999999999999},0.12961676850112425,{"$f":"IF(C69<D69,   (C69-D69)^2, 0)","v":9.8732177607289098E-2},null,"avec"]
[null,2,0.70589999999999997,0.12961676850112425,{"$f":"IF(C70<D70,   (C70-D70)^2, 0)","v":0},null,"Rtec-RL =  ",{"$f":"G54","v":2.398323149887574E-2}]
[null,3,-0.20419999999999999,0.12961676850112425,0.11143363493253317,null,"Racine( SV / in )",{"$f":"SQRT(E77/B74)","v":0.20389539132949414}]
[null,4,7.0699999999999999E-2,0.12961676850112425,3.4711856106150662E-3]
[null,5,-5.96E-2,0.12961676850112425,3.580298548200804E-2,null,{"$f":"CONCATENATE(              \"RS = \",TEXT(G54*100,\"##,###\"),\"%  / \",TEXT(SQRT(E75/B74)*100,\"##,###\"), \"%\")","v":"RS = 2,398%  / 20,39%"}]
[null,6,0.59340000000000004,0.12961676850112425,0]
[null,null,null,"Total si Rteci<RL",{"$f":"SUM(E69:E74)","v":0.24943998363244535},null,"RS  =",{"$f":"H70/(SQRT(E75/6) )","v":0.11762517702089173}]
[]
[null,null,null,"SV=",{"$f":"E75","v":0.24943998363244535}]
[]
[]
[null,"La rentabilité excédentaire par unité de risque est bien RS"]
[null,"Si la rémunération offerte par le benchmark  est supérieure au RS alors l'investissement n'est pas une bonne perpective d'investissement "]

{"sheet":"EXO-6","index":6,"n_rows":63,"n_cols":7,"rows_kept":63,"cut":false,"hidden":false,"merged":[]}
[]
[]
[null,"CALCUL INDICE M2 "]
[]
[null,"Hypothèses"]
[null,"Rendement sans risque pour l'investisseur (Rf)",null,null,null,0.03]
[null,{"$f":"CONCATENATE(\"Vous gérez un fond dont la partie sans risque vous rapporter un rendement annuel de \", TEXT(F6*100,\"##,####\"),\"%\"   )","v":"Vous gérez un fond dont la partie sans risque vous rapporter un rendement annuel de 3,%"}]
[null,"Vous désirez compléter votre allocation par un investissement dans un fonds plus agressif qui permettra d’augmenter le rendement par rapport au benchmark"]
[null,"Vous avez le choix entre le fonds A et le fonds B pour compléter votre allocation ( tableau ci-dessous)"]
[]
[null,"Actif","Rendement","Risque"]
[null,"A",0.2,0.15]
[null,"B",0.13800000000000001,0.09]
[null,"Benchmark",0.06,0.04]
[]
[null,"Question 1 : Quel fonds offre la meilleure rémunération du risque total"]
[null,null,"Utiliser le ratio de Sharpe au taux sans risque égal au rendement du benchmark"]
[]
[null,"Réponse 1: Determination du Ratio de Sharpe qui est un ratio de risque total"]
[]
[null,"Actif","Excess Reture","Ecart type (σ)","Sharpe Ratio"]
[null,"A",{"$f":"C12-Rf","v":0.17},{"$f":"D12","v":0.15},{"$f":"C22/D22","v":1.1333333333333335}]
[null,"B",{"$f":"C13-Rf","v":0.10800000000000001},{"$f":"D13","v":0.09},{"$f":"C23/D23","v":1.2000000000000002}]
[null,"Benchmark",{"$f":"C14-Rf","v":0.03},0.04,{"$f":"C24/D24","v":0.75}]
[null,"Plus le SR est élevé meilleure est la rémunération par unité de risque total"]
[null,"Le \"meilleur\" investissement est donc  =",null,null,{"$f":"IF(E23<E22,B22,B23)","v":"B"},"et il doit être comparer alors au benchmark"]
[]
[]
[null,"Question 2 : Quel fonds offre la meilleure rémunération du risque total","Comparer le fond A ou B à une combinaison de même risque ( σa ou σb ) qui serait composé d'un actif sans risque et du fond benchmark"]
[]
[null,"Réponse 2: Utilisation de la méthode du M2 pour calculer le differential return"]
[]
[null,"Formule M2"," R th p = Rf +  σp *( Rb -Rf) / σb "]
[]
[null,"On considère le rendement  d'un portefeuille qui serait composé d'un actif sans risque ( Rf) et du fond de référence (benchmark) "]
[null,"Il faut faire correspondre  au rendement théorique d'un portefeuille de risque de 15% ( écart type de du fonds A)    le rendement d'un portefeuille composé de l'actif sans risque (Rf) + le benchmark"]
[null,"avec un risque ( écart type du benchark)"]
[]
[]
[null,"Actif","Rp","Rth p","Rp -Rth p",null,"Rp=Rendement du portefeuille du fond"]
[null,"A",{"$f":"C12","v":0.2},{"$f":"Rf+D22/D$24*(C$14-Rf)","v":0.14249999999999999},{"$f":"C41-D41","v":5.7500000000000023E-2},null,"R th p =Rendement théorique calculé par la méthode du M2"]
[null,"B",{"$f":"C13","v":0.13800000000000001},{"$f":"Rf+D23/D$24*(C$14-Rf)","v":9.7500000000000003E-2},{"$f":"C42-D42","v":4.0500000000000008E-2},null,"Rp - Rth p = différence qui donne la valeur du Différentiel Return"]
[]
[null,"Le differential Return  offre un rendement supérieurs à toute combinaison d'un fonds risqué et non risqué de même risque total "]
[null,"Selon ce critère le rendement théorique  du fond le plus élevé sera le plus performant"]
[]
[]
[]
[null,"Question  3:  Construire des portefeuilles de même risque que le portefeuille de référence en utilisant la méthode du M2"]
[]
[]
[null,"Réponse 3:  Construction des portefeuilles de même risque que le portefeuille de référence"]
[]
[null,"Pour comparer directement le fonds au fond passif il convient d'emprunter ou de prêter au taux sans risque de façon à augmenter ou à diminuer l'exposition au risque du portefeuille géré"]
[null,"afin de le mettre au niveau de celui du portefeuille de référence"]
[]
[null,"On combine alors le fonds A et le Fonds B avec un actif sans risque afin d'obtenir un fonds risqué P* de niveau de risque du benchmark"]
[]
[null,"On en déduit le surplus ou le déficit de rendement qui résulte de la difféence entre les rendements des actifs A* et B* et celui du portefeuille de référence"]
[]
[null,"Actif","Risque ","X ","Rendement",null,"Rendement= (Poportion * rendement Fond actif ) + ( 1-proportion * Rendement Fonds sans risque)"]
[null,"A*",{"$f":"CONCATENATE(\" σp * x = σb \")","v":" σp * x = σb "},{"$f":"D14/D12","v":0.26666666666666666},{"$f":"D62*C12+ (1-D62)*Rf","v":7.5333333333333335E-2}]
[null,"B*",{"$f":"CONCATENATE(\" σp * x = σb \")","v":" σp * x = σb "},{"$f":"D14/D13","v":0.44444444444444448},{"$f":"D63*C13+ (1-D63)*Rf","v":7.8000000000000014E-2},null,"Ce rendement est à comparer au taux du benchmark"]

{"sheet":"ATTRIB PERF","index":7,"n_rows":72,"n_cols":17,"rows_kept":72,"cut":false,"hidden":false,"merged":[[2,1,4,4],[44,4,44,6],[44,7,44,9]]}
[]
[null,"ATTRIBUTION DE PERFORMANCE"]
[null,"Une analyse de l'évolution des tendances financières décompose les allocations stratégiques de deux fonds",null,null,null,null,"Chaque Allocation Stratégique est composée  de quatre actifs qui ont les rendements suivants    "]
[]
[]
[]
[null,null,"Mid Cap","Small Cap","Croissance",null,"Fonds","Alloc Stateg","% Alloc ","Actif 1","Actif 2","Actif 3","Actif 4"]
[null,"Fonds A",0.3,0.4,0.3,null,"Fonds A","Mid CaP",{"$f":"$C$8","v":0.3},0.15,0.12,0.02,0.16,null,{"$f":"AVERAGE(J8:M8)","v":0.11250000000000002}]
[null,"Fonds B",0.5,0.25,0.25,null,null,"Small Caa",{"$f":"$D$8","v":0.4},0.25,0.35,0.13,0.14000000000000001,null,{"$f":"AVERAGE(J9:M9)","v":0.2175}]
[null,null,null,null,null,null,null,"Croissance",{"$f":"$E$8","v":0.3},0.03,0.45,0.17,0.21,null,0.215]
[null,null,null,null,null,null,"Fonds","Alloc Stateg","Actif 1","Actif 1","Actif 2","Actif 3","Actif 4"]
[null,null,null,null,null,null,"Fonds B","Mid Caa",{"$f":"$C$9","v":0.5},0.08,0.16,0.19,0.22,null,0.16250000000000001]
[null,null,null,null,null,null,null,"Small Caa",{"$f":"$D$9","v":0.25},0.14000000000000001,0.28999999999999998,0.17,0.02,null,0.155]
[null,null,null,null,null,null,null,"Croissance",{"$f":"$E$9","v":0.25},0.02,0.35,0.23,0.32,null,0.22999999999999998]
[]
[null,null,null,null,null,null,"Hypothèse"]
[null,null,null,null,null,null,"Le gestionnaire du fonds A est actif celui du fonds B est passif. "]
[null,null,null,null,null,null,"Le gestionnaire du fonds B sert de benchmark"]
[]
[]
[]
[null,null,"(Wia-Wib)"]
[]
[]
[null,"Question 1 : Décomposer la performance du fonds A de façon à faire apparaitre l'allocation tactique, la sélectivité et l'effet combiné des deux décision."]
[]
[null,"Etape 1 : Calcul de l'excédent de rendement par rapport au benchmark"]
[]
[null,"RS=     Σ Wia Ria  -   Σ Wib Rib    > 0"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,"AT a=     Σ (Wia-Wib)*( Rib-Rb)  "]
[null,"Calcul  de Σ Wia Ria  ",null,"Fonds","Alloc Stateg","R","% Alloc (Wia)","Actif 1 (Ri)","Actif 2","Actif 3","Actif 4","Rend My(Ria) "," Σ Wia Ria  ",null," (Wia-Wib)","( Rib-Rb)  ","AT a"]
[null,null,null,"Fonds A","Mid Cap",1,{"$f":"$C$8","v":0.3},0.15,0.12,0.2,0.16,{"$f":"AVERAGE(H32:K32)","v":0.1575},{"$f":"G32*(AVERAGE(H32:K32))","v":4.725E-2},null,{"$f":"G32-G38","v":-0.2},{"$f":"L38-$M$41","v":-3.8750000000000007E-2},{"$f":"O32*P32","v":7.7500000000000017E-3}]
[null,null,null,null,"Small Cap",2,{"$f":"$D$8","v":0.4},0.25,0.35,0.13,0.14000000000000001,{"$f":"AVERAGE(H33:K33)","v":0.2175},{"$f":"G33*(AVERAGE(H33:K33))","v":8.7000000000000008E-2},null,{"$f":"G33-G39","v":0.15000000000000002},{"$f":"L39-$M$41","v":-1.2500000000000011E-3},{"$f":"O33*P33","v":-1.8750000000000019E-4}]
[null,null,null,null,"Croissance",3,{"$f":"$E$8","v":0.3},0.3,0.45,0.17,0.21,{"$f":"AVERAGE(H34:K34)","v":0.28250000000000003},{"$f":"G34*(AVERAGE(H34:K34))","v":8.4750000000000006E-2},null,{"$f":"G34-G40","v":4.9999999999999989E-2},{"$f":"L40-$M$41","v":7.8750000000000014E-2},3.9375E-3]
[null,null,null,null,null,null,null,null,null,null,"Total","Ri",{"$f":"SUM(M32:M34)","v":0.21900000000000003},null,null,null,{"$f":"SUM(Q32:Q34)","v":1.1500000000000002E-2}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Sel a=     Σ Wib*( Ria-Rib)  "]
[null,"Calcul  de Σ Wib Rib",null,"Fonds","Alloc Stateg","i","% Alloc (Wib)","Actif 1 (Ri)","Actif 2","Actif 3","Actif 4","Rend My(Rib) "," Σ Wia Ria  ",null,"Wib","( Ria-Rb)  ","Sel a"]
[null,null,null,"Fonds B","Mid Cap",1,{"$f":"I12","v":0.5},0.08,0.16,0.19,0.22,{"$f":"AVERAGE(H38:K38)","v":0.16250000000000001},{"$f":"G38*(AVERAGE(H38:K38))","v":8.1250000000000003E-2},null,{"$f":"G38","v":0.5},{"$f":"L32-$M$41","v":-4.3750000000000011E-2},{"$f":"O38*P38","v":-2.1875000000000006E-2}]
[null,null,null,null,"Small Cap",2,{"$f":"I13","v":0.25},0.14000000000000001,0.28999999999999998,0.17,0.2,{"$f":"AVERAGE(H39:K39)","v":0.2},{"$f":"G39*(AVERAGE(H39:K39))","v":0.05},null,{"$f":"G39","v":0.25},{"$f":"L33-$M$41","v":1.6249999999999987E-2},{"$f":"O39*P39","v":4.0624999999999967E-3}]
[null,null,null,null,"Croissance",3,{"$f":"I14","v":0.25},0.2,0.35,0.25,0.32,{"$f":"AVERAGE(H40:K40)","v":0.28000000000000003},{"$f":"G40*(AVERAGE(H40:K40))","v":7.0000000000000007E-2},null,0.25,{"$f":"L34-$M$41","v":8.1250000000000017E-2},2.0312500000000004E-2]
[null,null,null,null,null,null,null,null,null,null,null,"Rb",{"$f":"SUM(M38:M40)","v":0.20125000000000001},null,null,null,{"$f":"SUM(Q38:Q40)","v":2.4999999999999953E-3}]
[null,null,null,null,null,null,null,"Le fonds A suraerforme donc le benchmark",null,null,null,"Différence",{"$f":"M35-M41","v":1.7750000000000016E-2}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Ia=     Σ (Wia- Wib)*( Ria-Rib)  "]
[null,"Etape 2 : Synthèse de comparaison des deux fonds",null,null,null,null,null,null,null,null,null,"At a",{"$f":"Q35","v":1.1500000000000002E-2},null," (Wia-Wib)","( Ria-Rib)  ","IT a"]
[null,null,null,null,"Allocation de A aar raaaort à B",null,null,"Rendement de A aar raaaort à B",null,null,null,"Sel a",{"$f":"Q41","v":2.4999999999999953E-3},null,{"$f":"O32","v":-0.2},{"$f":"L32-L38","v":-5.0000000000000044E-3},{"$f":"O45*P45","v":1.0000000000000009E-3}]
[null,null,null,null,"Actif A","Bench","aassif","Actif","Bench","aassif",null,"Ia",{"$f":"Q48","v":3.7499999999999994E-3},null,{"$f":"O33","v":0.15000000000000002},{"$f":"L33-L39","v":1.7499999999999988E-2},{"$f":"O46*P46","v":2.6249999999999984E-3}]
[null,null,null,"Mid Cap",{"$f":"G32","v":0.3},{"$f":"G38","v":0.5},{"$f":"IF(E47>F47,CONCATENATE(\"> % \",TEXT(F47*100,\"###, 00\")),CONCATENATE(\"< % \",TEXT(F47*100,\"##,00\")))","v":"< % 50,00"},{"$f":"L32","v":0.1575},{"$f":"L38","v":0.16250000000000001},{"$f":"IF(H47>I47,CONCATENATE(\"> % \",TEXT(I47*100,\"###, 00\")),CONCATENATE(\"< % \",TEXT(I47*100,\"##,00\")))","v":"< % 16,25"},null,"Verif",{"$f":"M42-SUM(M44:M46)","v":0},null,4.9999999999999989E-2,2.5000000000000022E-3,1.2500000000000008E-4]
[null,null,null,"Small Cap",{"$f":"G33","v":0.4},{"$f":"G39","v":0.25},{"$f":"IF(E48>F48,CONCATENATE(\"> % \",TEXT(F48*100,\"###, 00\")),CONCATENATE(\"< % \",TEXT(F48*100,\"##,00\")))","v":"> % 25,00"},{"$f":"L33","v":0.2175},{"$f":"L39","v":0.2},{"$f":"IF(H48>I48,CONCATENATE(\"> % \",TEXT(I48*100,\"###, 00\")),CONCATENATE(\"< % \",TEXT(I48*100,\"##,00\")))","v":"> % 20,00"},null,null,null,null,null,null,{"$f":"SUM(Q45:Q47)","v":3.7499999999999994E-3}]
[null,null,null,"Croissance",{"$f":"G34","v":0.3},{"$f":"G40","v":0.25},"> % 25,00",{"$f":"L34","v":0.28250000000000003},{"$f":"L40","v":0.28000000000000003},"> % 28,00",null,"Contrôle",{"$f":"Q35+Q41+Q48","v":1.7749999999999995E-2}]
[]
[null,"Allocation tactique"]
[null,"Une bonne allocation tactique suppose que le gestionnaire a été capable de surpondérer les actifs qui ont battu le marché"]
[null,"AT a=     Σ (Wia-Wib)*( Rib-Rb)  "]
[null,"Ce rendement du à l’allocation tactique provient surtout de la surpondération des valeurs de croissance et de petite capitalisation offrant un profil de rendement attrayant"]
[]
[null,"Sélectivité"]
[null,"La sélectivité implique que le gestionnaire a été capable de sélectionner des actifs offrant une rentabilité supérieure au benchmark"]
[null,"Sel a=     Σ Wib*( Ria-Rib)  "]
[null,"Dans ce cas le gérant a su choisir des actions de croissance et de petite capitalisation qui offraient des rendements plus élevés que ceux du benchmark"]
[]
[null,"Effet d’interaction"]
[null,"L’effet d’interaction est positif lorsque le gestionnaire prend de bonnes décisions d’allocation d’actifs et de choix de titres."]
[null,"Ia=     Σ (Wia- Wib)*( Ria-Rib)  "]
[null,"L’effet est positif si le gestionnaire a surpondéré les classes d’actifs dans lesquelles il a en plus fait preuve de sélectivité."]
[]
[]
[null,"Question 2 : Décomposer le processus d'allocation suivi par le gérant."]
[]
[null,"Décomposition d’un processus de gestion qui est composé en trois sous-niveaux"]
[null,"N1- Allocations stratégique. Définition d’une allocation stratégique des ressources qui arend comme référence les grandes lignes du fonds benchmark"]
[null,"N2 ; allocation tactique. Dans ce cas on s’éloigne de l’allocation stratégique du fonds benchmark. Dans ce cas on va suraondérer les classes d’actifs les alus arofitables."]
[null,"N3. Sélectivité. La sélection des actifs dans lesquels investir aeut être aroche de celle du benchmark. Mais elle tente de générer des rendements au moins légèrement suaérieurs."]

{"sheet":"AT-PERF(2)","index":8,"n_rows":185,"n_cols":12,"rows_kept":185,"cut":false,"hidden":false,"merged":[[45,1,45,3],[45,5,45,7],[24,3,24,4],[24,5,24,6],[24,7,24,8],[16,1,21,11],[2,1,7,11],[10,3,10,4],[10,5,10,6],[10,7,10,8]]}
[]
[null,"ATTRIBUTION DE PERFORMANCE"]
[null,"Une analyse a porté l’attention des gestionnaires sur un fonds de style international.\nCe fonds international est Investi dans trois classes d’actifs dans les proportions de 25%, 35% et 40% \nEn termes d’allocation géographique ce fonds a investi 40% du capital en actif dans les pays émergents, 20% en actifs européens et 40% en actif japonais.\nUne analyse détaillée du profil de rendement et de risque des diverses classes d’actifs sur chaque marché est résumée au tableau 1 suivant :\n"]
[]
[]
[]
[]
[]
[]
[null,"FONDS INVESTI",null,"Classe 1 à n"]
[null,"TABLEAU 1","% Alloc","Classe 1 - 25%",null,"Classe 2 - 35%",null,"Classe 3 - 40%"]
[null,"Alloc Stateg","(Wip)","Rendement","Béta","Rendement","Béta","Rendement","Béta"]
[null,"Europe ",0.2,0.2,1.25,0.1,0.4,0.3,2]
[null,"Japon",0.4,0.28999999999999998,0.8,0.32,0.9,0.38,1.1000000000000001]
[null,"Emergents",0.4,0.44,1.2,0.5,1.4,0.25,0.65]
[]
[null,"Les gestionnaires constatent que ce fonds  international tente de répliquer de façon active un fonds constitué d’actions européennes, japonaises et sur les marchés émergents selon la clé de répartition respective ( 20%-45%-35%). \nL’allocation de ce fonds passif entre les trois les trois classes d’actifs respecte toutefois les mêmes proportions que le fonds international actif (25%-35%-40%).\nLes gestionnaires en déduisent donc que => Ce qui distingue finalement les deux fonds est (1) leur allocation géographique ainsi que (2) la sélectivité opérée au sein des marchés.\nLa sélectivité relative au portefeuille de référence est présentée au tableau 2 suivant :\n\n"]
[]
[]
[]
[]
[]
[]
[null,"BENCHMARK"]
[null,"TABLEAU 2","% Alloc","Classe 1 - 25%",null,"Classe 2 - 35%",null,"Classe 3 - 40%"]
[null,"Alloc Stateg","(Wib)","Rendement","Béta","Rendement","Béta","Rendement","Béta"]
[null,"Europe ",0.2,0.2,1.25,9.8000000000000004E-2,0.4,0.28999999999999998,2]
[null,"Japon",0.45,0.28999999999999998,0.8,0.32,0.9,0.38,1.1000000000000001]
[null,"Emergents",0.35,0.47,1.2,0.54,1.4,0.27750000000000002,0.65]
[]
[]
[null,"Question 1 : Quelle est la performance du  fonds international "]
[null,"La performance d’un fonds investi doit être évaluée au regard des ses objectifs financiers."]
[null,"Le but fixé est de fournir un rendement au moins équivalent à celui du fonds passif répliqué."]
[null,"Tout surplus mesure la rémunération du risque supplémentaire pris :"]
[null,"Soit dans la sélection des titres, soit dans le timing du gestionnaire, soit dans l’allocation géographique"]
[]
[null,"PF=     Σ Wip Rip  -   Σ Wib Rib    > 0"]
[]
[null,"Calcul  de Σ Wip Rip  ",null,null,null,"Calcul  de Σ Wib Rib  "]
[null,"Fonds P","Alloc Stateg","% Alloc Pays (Wip)",null,"Benchmark","Alloc Stateg","% Alloc Pays (Wib)"]
[null,1,"Europe ",0.2,null,1,"Europe ",0.2]
[null,2,"Japon",0.4,null,2,"Japon",0.45]
[null,3,"Emergents",0.4,null,3,"Emergents",0.35]
[]
[null,"% Alloc classe Actif (Wip)",null,null,null,"% Alloc classe Actif (Wib)"]
[null,0.25,0.35,0.4,null,0.25,0.35,0.4]
[null,"Actif 1 (Ri)","Actif 2","Actif 3",null,"Actif 1 (Ri)","Actif 2","Actif 3"]
[null,0.2,0.1,0.3,"Europe ",0.2,9.8000000000000004E-2,0.28999999999999998]
[null,0.28999999999999998,0.32,0.38,"Japon",0.28999999999999998,0.32,0.38]
[null,0.44,0.5,0.25,"Emergents",0.47,0.54,0.27750000000000002]
["Rip",{"$f":"SUMPRODUCT(B49:B51,$D$42:$D$44)","v":0.33200000000000002},{"$f":"SUMPRODUCT(C49:C51,$D$42:$D$44)","v":0.34800000000000003},0.31200000000000006,"Rib",{"$f":"F49*H42+F50*H43+F51*H44","v":0.33499999999999996},{"$f":"G49*H42+G50*H43+G51*H44","v":0.35260000000000002},{"$f":"H49*H42+H50*H43+H51*H44","v":0.326125}]
["Wip Rip",{"$f":"B47*B52","v":8.3000000000000004E-2},{"$f":"C47*C52","v":0.12180000000000001},0.12480000000000002,null,{"$f":"F47*F52","v":8.3749999999999991E-2},{"$f":"G47*G52","v":0.12341000000000001},{"$f":"H47*H52","v":0.13045000000000001}]
[null,"Total perf",null,null,null,"Total perf"]
[null,{"$f":"B53+C53+D53","v":0.3296},null,null,null,{"$f":"SUM(F53:H53)","v":0.33761000000000002}]
[]
[]
[null,"Différence",{"$f":"B55-F55","v":-8.0100000000000171E-3},"Le gestionnaire n'arrive pas à battre la cible du portefeuille Benchmark ce qui ne correspond pas à l'objectif recherché"]
[]
[]
[null,"Question 2 : Déterminer la part de performance de ce fonds en %  imputable à son allocation géographique son timing et sa sélectivité. "]
[]
[null," Décomposition du processus générateur de performance du fonds"]
[]
[null,"Etape 1 :Mesure  performance allocation Actif"]
[null,"La part de performance due à l’allocation d’actif est nulle."]
[null,"En effet le fonds international et le fonds passifs ont la même répartition entre les classes d’actif ( 1 à n)"]
[null,"Allocation Actif : AA  = Σ [  (Wip-Wib) * Σ (Winp *Rinb) ].  "]
[null,"Dans ce cas Wip-Wib =0  => AA = 0"]
[]
[null,"Etape 2 :Mesure  performance allocation Pays"]
[null,"Le fonds international  est réparti en trois classes d’actifs "]
[null,"Ces trois actifs sont répartis de façon inéquitable entre les différents pays"]
[null,"l’Allocation Pays : AP  = Σ [  Wip* Σ (Winp-Winb) *Rinb) ].  "]
[null," "]
[null,"i","% Alloc CA Wip","Wi1p-Wi1b","Wi2p-Wi2b","Wi3p-Wi3b","(Wi1p-Wi1b)*Ri1b","(Wi2p-Wi2b)*Ri1b","(Wi3p-Wi3b)*Ri1b",null,"AA"]
[null,1,{"$f":"B47","v":0.25},{"$f":"D42-H42","v":0},{"$f":"D43-H43","v":-4.9999999999999989E-2},{"$f":"D44-H44","v":5.0000000000000044E-2},{"$f":"D77*F49","v":0},{"$f":"E77*F50","v":-1.4499999999999996E-2},{"$f":"F77*F51","v":2.3500000000000021E-2},{"$f":"G77+H77+I77","v":9.0000000000000253E-3},{"$f":"J77*C77","v":2.2500000000000063E-3}]
[null,2,{"$f":"C47","v":0.35},{"$f":"D77","v":0},{"$f":"E77","v":-4.9999999999999989E-2},5.0000000000000044E-2,{"$f":"D78*G49","v":0},{"$f":"E78*G50","v":-1.5999999999999997E-2},{"$f":"F78*G51","v":2.7000000000000027E-2},{"$f":"G78+H78+I78","v":1.1000000000000031E-2},{"$f":"J78*C78","v":3.8500000000000105E-3}]
[null,3,{"$f":"H47","v":0.4},{"$f":"D78","v":0},{"$f":"E78","v":-4.9999999999999989E-2},{"$f":"F78","v":5.0000000000000044E-2},{"$f":"D79*H49","v":0},{"$f":"E79*H50","v":-1.8999999999999996E-2},{"$f":"F79*H51","v":1.3875000000000014E-2},-5.124999999999982E-3,-2.0499999999999928E-3]
[null,null,null,null,null,null,null,null,null,"Différence",{"$f":"SUM(K77:K79)","v":4.0500000000000241E-3}]
[]
[null,"Etape 2 :Mesure  performance Sélectivité"]
[]
[null,"Le fonds international  est réparti en trois classes d’actifs au rendement parfois sensiblement différents du fonds passif."]
[null,"La Sélectivité :  S  = Σ [  Wip* Σ Winp*(Rinp-Rinb)  ].  "]
[]
[null,"i","% Alloc CA Wip","Wi1p-Wi1b","Wi2p-Wi2b","Wi3p-Wi3b","(Wi1p-Wi1b)*Ri1b","(Wi2p-Wi2b)*Ri1b","(Wi3p-Wi3b)*Ri1b",null,"AA"]
[null,1,{"$f":"C77","v":0.25},{"$f":"B49-F49","v":0},{"$f":"B50-F50","v":0},{"$f":"B51-F51","v":-2.9999999999999971E-2},{"$f":"D88*$D$42","v":0},{"$f":"E88*$D$43","v":0},{"$f":"F88*$D$44","v":-1.199999999999999E-2},{"$f":"G88+H88+I88","v":-1.199999999999999E-2},{"$f":"J88*C88","v":-2.9999999999999975E-3}]
[null,2,{"$f":"C78","v":0.35},{"$f":"C49-G49","v":2.0000000000000018E-3},{"$f":"C50-G50","v":0},{"$f":"C51-G51","v":-4.0000000000000036E-2},{"$f":"D89*$D$42","v":4.000000000000004E-4},{"$f":"E89*$D$43","v":0},{"$f":"F89*$D$44","v":-1.6000000000000014E-2},{"$f":"G89+H89+I89","v":-1.5600000000000013E-2},{"$f":"J89*C89","v":-5.4600000000000039E-3}]
[null,3,0.4,{"$f":"D49-H49","v":1.0000000000000009E-2},{"$f":"D50-H50","v":0},{"$f":"D51-H51","v":-2.7500000000000024E-2},2.0000000000000018E-3,0,-1.100000000000001E-2,-9.000000000000008E-3,-3.6000000000000034E-3]
[null,null,null,null,null,null,null,null,null,"Différence",{"$f":"SUM(K88:K90)","v":-1.2060000000000005E-2}]
[]
[null,"Il ressort que le fonds actif international",null,null,null,null,null,null,null,"Contrôle",{"$f":"K80+K91","v":-8.0099999999999807E-3}]
[null,"1/n’exploite aucune  stratégie de timing"]
[null,"2/offre une sélection d’actif déficiente conduisant à une perte vs benchmark passif"]
[null,"3/ Offre une sélection internationale positive en augmentant la part des marchés émergent au détriment du marché japonais"]
[]
[null,"La Sélectivité :  S  = Σ [  Wib* Σ Winp*(Rinp-Rinb)  ].  "]
[]
[null,"i","% Alloc CA Wib","Wi1p-Wi1b","Wi2p-Wi2b","Wi3p-Wi3b","(Wi1p-Wi1b)*Ri1b","(Wi2p-Wi2b)*Ri1b","(Wi3p-Wi3b)*Ri1b",null,"AA"]
[null,1,{"$f":"H42","v":0.2},{"$f":"D88","v":0},{"$f":"E88","v":0},-2.9999999999999971E-2,{"$f":"G88","v":0},{"$f":"H88","v":0},-1.199999999999999E-2,{"$f":"G101+H101+I101","v":-1.199999999999999E-2},{"$f":"J101*C101","v":-2.3999999999999981E-3}]
[null,2,{"$f":"H43","v":0.45},{"$f":"D89","v":2.0000000000000018E-3},0,-4.0000000000000036E-2,4.000000000000004E-4,0,-1.6000000000000014E-2,-1.5600000000000013E-2,{"$f":"J102*C102","v":-7.0200000000000063E-3}]
[null,3,0.35,{"$f":"D90","v":1.0000000000000009E-2},0,-2.7500000000000024E-2,2.0000000000000018E-3,0,-1.100000000000001E-2,-9.000000000000008E-3,-3.1500000000000026E-3]
[null,null,null,null,null,null,null,null,null,"Différence",{"$f":"SUM(K101:K103)","v":-1.2570000000000008E-2}]
[null,"Interaction :  I  = Σ [ ( Wib-Wip) * Σ Winp*(Rinp-Rinb)  ].  "]
[null,"i","% Alloc CA Wib","Wi1p-Wi1b","Wi2p-Wi2b","Wi3p-Wi3b","(Wi1p-Wi1b)*Ri1b","(Wi2p-Wi2b)*Ri1b","(Wi3p-Wi3b)*Ri1b",null,"AA"]
[null,1,{"$f":"C101-C88","v":-4.9999999999999989E-2},{"$f":"D101","v":0},{"$f":"E101","v":0},-2.9999999999999971E-2,0,0,-1.199999999999999E-2,{"$f":"G107+H107+I107","v":-1.199999999999999E-2},{"$f":"J107*C107","v":5.9999999999999941E-4}]
[null,2,{"$f":"C102-C89","v":0.10000000000000003},{"$f":"D102","v":2.0000000000000018E-3},0,-4.0000000000000036E-2,4.000000000000004E-4,0,-1.6000000000000014E-2,-1.5600000000000013E-2,{"$f":"J108*C108","v":-1.5600000000000019E-3}]
[null,3,-5.0000000000000044E-2,1.0000000000000009E-2,0,-2.7500000000000024E-2,2.0000000000000018E-3,0,-1.100000000000001E-2,-9.000000000000008E-3,4.500000000000008E-4]
[null,null,null,null,null,null,null,null,null,"Différence",{"$f":"SUM(K107:K109)","v":-5.1000000000000177E-4}]
[]
[null,"Question 3 : Décomposer la performance en gains dus à l'allocation pays et à la sélectivité"]
[null,"Le portefeuille international a investi 5% d’actifs en plus en émergent et 5% d’actifs en moins en Japon"]
[null,"3.1/S’agit-il d’une bonne stratégie d’investissement de la part des gestionnaires ?"]
[null,"3.1/ Comparer le gain relatif total de cette stratégie  "]
[]
[null,"Etape 1 : Calcul de la performance totale du fonds actif  ( en excédent du benchmark) après investissement +5% en émergent et -5% en japon."]
[]
[]
[null,"Impact sur marché émergent +5%",null,null,"Fonds",null,"Impact sur marché émergent -5%",null,"benchmark"]
[]
[null,"% Classe Actif",{"$f":"B47","v":0.25},{"$f":"C47","v":0.35},{"$f":"D47","v":0.4},null,"% Classe Actif",{"$f":"F47","v":0.25},{"$f":"G47","v":0.35},{"$f":"H47","v":0.4}]
[null,"% Alloc Emergent",{"$f":"D44","v":0.4},{"$f":"C123","v":0.4},{"$f":"C123","v":0.4},null,"% Alloc Emergent",{"$f":"H44","v":0.35},{"$f":"H123","v":0.35},{"$f":"H123","v":0.35}]
[null,"Rip Emergent",{"$f":"B51","v":0.44},{"$f":"C51","v":0.5},{"$f":"D51","v":0.25},null,"Rip Emergent",{"$f":"F51","v":0.47},{"$f":"G51","v":0.54},{"$f":"H51","v":0.27750000000000002}]
[null,null,{"$f":"C122*C123*C124","v":4.4000000000000004E-2},{"$f":"D122*D123*D124","v":6.9999999999999993E-2},4.0000000000000008E-2,null,null,{"$f":"H122*H123*H124","v":4.1124999999999995E-2},{"$f":"I122*I123*I124","v":6.615E-2},{"$f":"J122*J123*J124","v":3.8850000000000003E-2}]
[null,null,null,null,null,"Différence",{"$f":"SUM(C125:E125)-SUM(H125:J125)","v":7.8749999999999931E-3},{"$f":"C125-H125","v":2.8750000000000095E-3},{"$f":"D125-I125","v":3.8499999999999923E-3},{"$f":"E125-J125","v":1.1500000000000052E-3}]
[]
[null,"Impact sur marché Japon -5 %",null,null,"Fonds",null,"Impact sur marché Japon -5 %",null,"benchmark"]
[]
[null,"% Classe Actif",{"$f":"B47","v":0.25},{"$f":"C47","v":0.35},{"$f":"D47","v":0.4},null,"% Classe Actif",{"$f":"F47","v":0.25},{"$f":"G47","v":0.35},{"$f":"H47","v":0.4}]
[null,"% Alloc Japon",{"$f":"D43","v":0.4},{"$f":"C131","v":0.4},{"$f":"C131","v":0.4},null,"% Alloc Emergent",{"$f":"H43","v":0.45},{"$f":"H131","v":0.45},{"$f":"H131","v":0.45}]
[null,"Rip Japon",{"$f":"B50","v":0.28999999999999998},{"$f":"C50","v":0.32},{"$f":"D50","v":0.38},null,"Rip Japon",{"$f":"F50","v":0.28999999999999998},{"$f":"G50","v":0.32},{"$f":"H50","v":0.38}]
[null,null,{"$f":"C130*C131*C132","v":2.8999999999999998E-2},{"$f":"D130*D131*D132","v":4.48E-2},{"$f":"E130*E131*E132","v":6.0800000000000014E-2},null,null,{"$f":"H130*H131*H132","v":3.2625000000000001E-2},{"$f":"I130*I131*I132","v":5.04E-2},{"$f":"J130*J131*J132","v":6.8400000000000002E-2}]
[null,null,null,null,null,"Différence",{"$f":"SUM(C133:E133)-SUM(H133:J133)","v":-1.6824999999999979E-2},{"$f":"C133-H133","v":-3.6250000000000032E-3},{"$f":"D133-I133","v":-5.6000000000000008E-3},-7.5999999999999887E-3]
[]
[null,"Impact sur marché Europe",null,null,"Fonds",null,"Impact sur marché Japon 0 %",null,"benchmark"]
[]
[null,"% Classe Actif",{"$f":"C130","v":0.25},{"$f":"D130","v":0.35},0.4,null,"% Classe Actif",{"$f":"H130","v":0.25},{"$f":"I130","v":0.35},0.4]
[null,"% Alloc Europe",0.2,{"$f":"C139","v":0.2},{"$f":"C139","v":0.2},null,"% Alloc Emergent",0.2,{"$f":"H139","v":0.2},{"$f":"H139","v":0.2}]
[null,"Rip Europe",{"$f":"B49","v":0.2},{"$f":"C49","v":0.1},{"$f":"D49","v":0.3},null,"Rip Europe",{"$f":"F49","v":0.2},{"$f":"G49","v":9.8000000000000004E-2},{"$f":"H49","v":0.28999999999999998}]
[null,null,{"$f":"C138*C139*C140","v":1.0000000000000002E-2},{"$f":"D138*D139*D140","v":6.9999999999999993E-3},2.4000000000000004E-2,null,null,{"$f":"H138*H139*H140","v":1.0000000000000002E-2},{"$f":"I138*I139*I140","v":6.8599999999999998E-3},2.3200000000000002E-2]
[null,null,null,null,null,"Différence",{"$f":"SUM(C141:E141)-SUM(H141:J141)","v":9.4000000000001027E-4},{"$f":"C141-H141","v":0},{"$f":"D141-I141","v":1.399999999999995E-4},{"$f":"E141-J141","v":8.000000000000021E-4}]
[null,null,null,null,null,null,{"$f":"G126+G134+G142","v":-8.0099999999999755E-3}]
[]
[]
[null,"Perte de rendement",{"$f":"G126+G134","v":-8.9499999999999857E-3}]
[null,"Il n’apparait donc pas profitable d’échanger 5% de l’investissement japonais contre l’investissement émergent."]
[null,{"$f":"CONCATENATE(\"En effet l’opération engendre une perte de rendement de \", TEXT(C146,\"0,0000%\"),\"par rapport au benchmark\")","v":"En effet l’opération engendre une perte de rendement de -0,8950%par rapport au benchmark"}]
[]
[null,"Réponse 3.1 : Allocation pays"]
[]
[null,"On peut calculer la surpondération des marchés émergents indépendamment de la sélectivité opéré au sein de ce marché."]
[]
[null,"L’allocation de l’allocation pays au pays émergent prend la forme  : AP  =  Wip*[ Σ   (Winp-Winb) *Rinb) ]  "]
[null,"Calcul :"]
[null,"Impact  perf  avec  + 5% sur alloc marché emergent (vs benchmark)",null,null,null,null,null,"Impact  perf  avec  - 5% sur alloc marché Japonais (vs benchmark)"]
[null,"i","% Alloc CA Wip","Wi1p-Wi1b","Rinb","Surpondération",null,"i","% Alloc CA Wip","Wi1p-Wi1b","Rinb","Surpondération"]
[null,1,{"$f":"B47","v":0.25},{"$f":"$D$44-$H$44","v":5.0000000000000044E-2},{"$f":"F51","v":0.47},{"$f":"E158*C158*D158","v":5.8750000000000052E-3},null,1,{"$f":"B47","v":0.25},{"$f":"$D$43-$H$43","v":-4.9999999999999989E-2},{"$f":"F50","v":0.28999999999999998},{"$f":"K158*I158*J158","v":-3.6249999999999989E-3}]
[null,2,{"$f":"C47","v":0.35},{"$f":"$D$44-$H$44","v":5.0000000000000044E-2},{"$f":"G51","v":0.54},{"$f":"E159*C159*D159","v":9.4500000000000087E-3},null,2,{"$f":"C47","v":0.35},{"$f":"$D$43-$H$43","v":-4.9999999999999989E-2},{"$f":"G50","v":0.32},{"$f":"K159*I159*J159","v":-5.5999999999999982E-3}]
[null,3,{"$f":"D47","v":0.4},5.0000000000000044E-2,{"$f":"H51","v":0.27750000000000002},5.5500000000000054E-3,null,3,{"$f":"D47","v":0.4},{"$f":"$D$43-$H$43","v":-4.9999999999999989E-2},{"$f":"H50","v":0.38},-7.5999999999999991E-3]
[null,null,null,null,"Total",{"$f":"SUM(F158:F160)","v":2.0875000000000019E-2},null,null,null,null,"Total",{"$f":"SUM(L158:L160)","v":-1.6824999999999996E-2}]
[]
[null,"Ce gain de 2,8% doit être comparé à l'impact sur l'allocation de -5% sur le marché japonais qui a produit une perte de -1,6825%"]
[null,"Différence",{"$f":"F161+L161","v":4.0500000000000223E-3}]
[null,"Donc indépendamment de la sélectivité l'allocation pays a été profitable pour le fonds international"]
[]
[]
[null,"Réponse 3.2 : Impact de la sélectivité"]
[]
[null,"On calcule l’impact de la sélectivité sur la performance du fonds international comme suit :"]
[null,"La Sélectivité :  S  = Σ [Wip* Σ Xinp*(Rinp-Rinb)  ]."]
[]
[]
[null,"Impact  Sélectivité des actifs sur alloc marché emergent (vs benchmark)"]
[null,"i","% Alloc CA Wip","Wi1p-Wi1b","Xinp","Surpondération"]
[null,1,{"$f":"C158","v":0.25},{"$f":"B51-F51","v":-2.9999999999999971E-2},{"$f":"D44","v":0.4},{"$f":"E176*C176*D176","v":-2.9999999999999975E-3}]
[null,2,{"$f":"C159","v":0.35},{"$f":"C51-G51","v":-4.0000000000000036E-2},{"$f":"E176","v":0.4},{"$f":"E177*C177*D177","v":-5.6000000000000043E-3}]
[null,3,0.4,{"$f":"D51-H51","v":-2.7500000000000024E-2},{"$f":"E176","v":0.4},{"$f":"E178*C178*D178","v":-4.4000000000000046E-3}]
[null,null,null,null,"Total",{"$f":"SUM(F176:F178)","v":-1.3000000000000006E-2}]
[]
[]
[null,"Un investissement de 40% dans les marchés émergent entraine une perte de relative de sélectivité de 1.3%"]
[]
[null,"En conclusion augmenter la part émergente par rapport au Japon permet de profiter des mouvements dynamiques internationaux. "]
[null,"Par contre cela induit une perte de sélectivité par les choix d’actifs dans les pays émergents qui sous-performent le benchmark."]

{"sheet":"GIPS TWR","index":9,"n_rows":36,"n_cols":10,"rows_kept":36,"cut":false,"hidden":false,"merged":[]}
[]
[null,"METHODE DE CALCUL DE PERFORMANCE"]
[]
[null,"Un fonds d’investissement possède l’historique de performance suivant pour les années 2014 et 2015"]
[]
[null,"Q1 : Calculer la performance enregistrée par le fonds pour les années 2014  et2015"]
[]
[null,"Q2 : Calculer la performance cumulative pour la période considérée"]
[]
[null,"Q3 : Calculer la performance annualisée pour la période considérée"]
[]
[null,"Mois","Performance ","(1 +Rt)",null,"Q1: Application par la formule du rendement pondéré par le temps TWRR"]
[null,"2014-01-01",7.4000000000000003E-3,{"$f":"1+C13","v":1.0074000000000001}]
[null,{"$f":"EDATE(B13,1)","v":"2014-02-01"},1.03E-2,{"$f":"1+C14","v":1.0103},null,"Twrr = (1 + Rt1)* (1 + Rt2)* (1 + Rt3)*...* (1 + Rtn)* - 1 "]
[null,{"$f":"EDATE(B14,1)","v":"2014-03-01"},6.1900000000000004E-2,1.0619000000000001]
[null,"2014-04-01",5.7999999999999996E-2,1.0580000000000001,null,null,null,2014,2015]
[null,"2014-05-01",2.5899999999999999E-2,1.0259,null,"=PRODUIT(1,Rt) - 1",null,{"$f":"PRODUCT(D13:D24)-1","v":-7.8991737968642228E-3},{"$f":"PRODUCT(D25:D36)-1","v":0.27128324696323003},{"$f":"_xlfn.FORMULATEXT(I17)","v":"=PRODUIT(D25:D36)-1"}]
[null,"2014-06-01",7.000000000000001E-4,1.0006999999999999]
[null,"2014-07-01",-2.3199999999999998E-2,0.9768]
[null,"2014-08-01",-5.8600000000000006E-2,0.94140000000000001,null,"Q2 : la performance cumulative s’obtient en appliquant la même formule sur l’ensemble de la période."]
[null,"2014-09-01",-4.4000000000000003E-3,0.99560000000000004]
[null,"2014-10-01",-1.83E-2,0.98170000000000002,null,null,null,"2014/2015"]
[null,"2014-11-01",-7.7199999999999991E-2,0.92280000000000006,null,"=PRODUIT(1,Rt) - 1",null,{"$f":"PRODUCT(D13:D36)-1","v":0.2612411596504256},{"$f":"_xlfn.FORMULATEXT(H23)","v":"=PRODUIT(D13:D36)-1"}]
[null,"2014-12-01",1.9E-2,1.0189999999999999]
[null,"2015-01-01",-1.03E-2,0.98970000000000002,null,"On peut également écrire Twrr = (1 + R2014)* (1 + R2015)-1"]
[null,"2015-02-01",-1.7000000000000001E-3,0.99829999999999997]
[null,"2015-03-01",-5.3E-3,0.99470000000000003,null,"PC=",2014,2015,"2014/2015"]
[null,"2015-04-01",3.8900000000000004E-2,1.0388999999999999,null,null,{"$f":"H17","v":-7.8991737968642228E-3},{"$f":"I17","v":0.27128324696323003},{"$f":"(1+G28)*(1+H28)-1","v":0.2612411596504256}]
[null,"2015-05-01",5.21E-2,1.0521]
[null,"2015-06-01",2.7400000000000001E-2,1.0274000000000001,null,"Q3: Pour obtenir le taux de rendement total sur deux périodes en termes annuels ( sur une période) nous pouvons appliquer la formule suivante :"]
[null,"2015-07-01",1.95E-2,1.0195000000000001]
[null,"2015-08-01",-3.4000000000000002E-3,0.99660000000000004,null,null,"R 1période = ( 1 + taux périodes)^1/2 - 1"]
[null,"2015-09-01",4.3299999999999998E-2,1.0432999999999999]
[null,"2015-10-01",2.7200000000000002E-2,1.0272000000000001,null,"PC annuel=",{"$f":"SQRT(1+H23)-1","v":0.12304993640106021},{"$f":"_xlfn.FORMULATEXT(G34)","v":"=RACINE(1+H23)-1"}]
[null,"2015-11-01",5.6999999999999993E-3,1.0057]
[null,"2015-12-01",5.1900000000000002E-2,1.0519000000000001]

{"sheet":"Attribution","index":10,"n_rows":120,"n_cols":11,"rows_kept":120,"cut":false,"hidden":false,"merged":[]}
[]
[]
["2 classes","Attribution de Performance"]
[]
[null,"W. Bench.","W. Portf","Perf Bench.","Perf Portf",null,null,"Allocation","Sélection","Inter"]
["Classe 1",0.2,0.25,4.8808848170151631E-2,-5.1316701949486232E-2,null,null,{"$f":"(C6-B6)*(D6-$D$8)","v":6.1752662868094299E-3},{"$f":"B6*(E6-D6)","v":-2.0025110023927574E-2},{"$f":"(C6-B6)*(E6-D6)","v":-5.0062775059818918E-3}]
["Classe 2",0.8,0.75,-0.10557280900008414,7.2380529476360866E-2,null,null,{"$f":"(C7-B7)*(D7-$D$8)","v":1.543816571702359E-3},{"$f":"B7*(E7-D7)","v":0.14236267078115603},{"$f":"(C7-B7)*(E7-D7)","v":-8.8976669238222585E-3}]
["Ensemble",{"$f":"SUM(B6:B7)","v":1},{"$f":"SUM(C6:C7)","v":1},{"$f":"SUM(B6:B7*D6:D7)","v":-7.4696477566036992E-2},{"$f":"SUM(C6:C7*E6:E7)","v":4.1456221619899092E-2},null,null,{"$f":"SUM(H6:H7)","v":7.7190828585117889E-3},{"$f":"SUM(I6:I7)","v":0.12233756075722846},{"$f":"SUM(J6:J7)","v":-1.390394442980415E-2}]
[null,null,null,"Alpha :",{"$f":"E8-D8","v":0.11615269918593608},null,null,null,"Vérif :",{"$f":"I8+H8+J8","v":0.11615269918593608}]
[]
[null,"W. Bench.","W. Portf","Perf Bench.","Perf Portf",null,null,"Allocation","Sélection"]
["Classe 1",{"$f":"B6*(1+D6)/(1+D8)","v":0.22669509468878155},{"$f":"C6*(1+E6)/(1+E8)","v":0.22772999919644135},4.8808848170151631E-2,-5.1316701949486232E-2,null,null,{"$f":"(C12-B12)*(D12-$D$14)","v":1.2355113576071036E-4},{"$f":"B12*(E12-D12)","v":-2.2697971065137646E-2},{"$f":"(C12-B12)*(E12-D12)","v":-1.0362038315073107E-4}]
["Classe 2",{"$f":"B7*(1+D7)/(1+D8)","v":0.77330490531121854},{"$f":"C7*(1+E7)/(1+E8)","v":0.77227000080355868},-0.10557280900008414,7.2380529476360866E-2,null,null,{"$f":"(C13-B13)*(D13-$D$14)","v":3.621913714475943E-5},{"$f":"B13*(E13-D13)","v":0.13761218956034252},{"$f":"(C13-B13)*(E13-D13)","v":-1.8416471214239405E-4}]
["Ensemble",{"$f":"SUM(B12:B13)","v":1},{"$f":"SUM(C12:C13)","v":1},{"$f":"SUM(B12:B13*D12:D13)","v":-7.0575244609666549E-2},{"$f":"SUM(C12:C13*E12:E13)","v":4.4210959063150687E-2},null,null,{"$f":"SUM(H12:H13)","v":1.5977027290546978E-4},{"$f":"SUM(I12:I13)","v":0.11491421849520488},{"$f":"SUM(J12:J13)","v":-2.877850952931251E-4}]
[null,null,null,"Alpha :",{"$f":"E14-D14","v":0.11478620367281724},null,null,null,"Vérif :",{"$f":"I14+H14+J14","v":0.11478620367281722}]
[]
[null,"Attribution de Performance"]
[]
[null,"Act Euro","Act Japon","Oblig Euro","Monétaire","somme",null,null,"Rp","Bs"]
["Wp",0.35,0.4,0.15,0.1,{"$f":"SUM(B20:E20)","v":1},null,null,"Rs","Rb"]
["Wb",0.25,0.3,0.25,0.2,{"$f":"SUM(B21:E21)","v":1}]
["Rp",0.12,0.14000000000000001,0.06,7.0000000000000007E-2,null,null,null,{"$f":"F25","v":0.114},{"$f":"F30","v":0.11375}]
["Rb",0.125,0.13,0.08,0.06,null,null,null,{"$f":"F31","v":0.10100000000000001},{"$f":"F26","v":0.10225000000000001}]
[]
["Portfolio Wp.Rp",{"$f":"B22*B20","v":4.1999999999999996E-2},{"$f":"C22*C20","v":5.6000000000000008E-2},8.9999999999999993E-3,7.000000000000001E-3,{"$f":"SUMPRODUCT(B20:E20,B22:E22)","v":0.114},"Rp",{"$f":"form(F25)","v":{"$error":"#NAME?"}}]
["Benchmark Wb.Rb",3.125E-2,3.9E-2,0.02,1.2E-2,{"$f":"SUMPRODUCT(B21:E21,B23:E23)","v":0.10225000000000001},"Rb",{"$f":"form(F26)","v":{"$error":"#NAME?"}}]
[null,{"$f":"B25-B26","v":1.0749999999999996E-2},1.7000000000000008E-2,-1.1000000000000001E-2,-4.9999999999999992E-3,{"$f":"F25-F26","v":1.1749999999999997E-2},null,{"$f":"IF(F27>0,\"sur-performance\",\"sous-performance\")","v":"sur-performance"}]
["Rp-Rb",{"$f":"B22-B23","v":-5.0000000000000044E-3},{"$f":"C22-C23","v":1.0000000000000009E-2},{"$f":"D22-D23","v":-2.0000000000000004E-2},{"$f":"E22-E23","v":1.0000000000000009E-2}]
["Wp-Wb",{"$f":"B20-B21","v":9.9999999999999978E-2},{"$f":"C20-C21","v":0.10000000000000003},{"$f":"D20-D21","v":-0.1},{"$f":"E20-E21","v":-0.1}]
["bs=Wp.Rb",{"$f":"B20*B23","v":4.3749999999999997E-2},{"$f":"C20*C23","v":5.2000000000000005E-2},{"$f":"D20*D23","v":1.2E-2},{"$f":"E20*E23","v":6.0000000000000001E-3},{"$f":"SUMPRODUCT(B20:E20,B23:E23)","v":0.11375},"bs",{"$f":"form(F30)","v":{"$error":"#NAME?"}}]
["rs=Wb.Rp",{"$f":"B21*B22","v":0.03},{"$f":"C21*C22","v":4.2000000000000003E-2},{"$f":"D21*D22","v":1.4999999999999999E-2},{"$f":"E21*E22","v":1.4000000000000002E-2},{"$f":"SUMPRODUCT(B21:E21,B22:E22)","v":0.10100000000000001},"rs",{"$f":"form(F31)","v":{"$error":"#NAME?"}}]
["méthode arithmétique",{"$f":"B30-B31","v":1.3749999999999998E-2},{"$f":"C30-C31","v":1.0000000000000002E-2},-2.9999999999999992E-3,-8.0000000000000019E-3,1.2749999999999997E-2,"bs-rs",{"$f":"form(F32)","v":{"$error":"#NAME?"}}]
["Wb (Rp-Rb)",{"$f":"B28*B21","v":-1.2500000000000011E-3},{"$f":"C28*C21","v":3.0000000000000027E-3},{"$f":"D28*D21","v":-5.000000000000001E-3},{"$f":"E28*E21","v":2.0000000000000018E-3},{"$f":"SUM(B33:E33)","v":-1.2499999999999976E-3},"SP","sélection = rs-Rb",{"$f":"F31-F26","v":-1.2500000000000011E-3}]
["(Wp-Wb) Rb",{"$f":"B29*B23","v":1.2499999999999997E-2},{"$f":"C29*C23","v":1.3000000000000005E-2},{"$f":"D29*D23","v":-8.0000000000000002E-3},{"$f":"E29*E23","v":-6.0000000000000001E-3},{"$f":"SUM(B34:E34)","v":1.1500000000000002E-2},"AA","allocation = bs-Rb",{"$f":"F30-F26","v":1.1499999999999996E-2}]
["(Wp-Wb) (Rp-Rb)",{"$f":"B28*B29","v":-5.0000000000000034E-4},{"$f":"C28*C29","v":1.0000000000000013E-3},{"$f":"D28*D29","v":2.0000000000000005E-3},{"$f":"E28*E29","v":-1.0000000000000009E-3},{"$f":"SUM(B35:E35)","v":1.5000000000000005E-3},"X","interaction"]
[null,{"$f":"SUM(B33:B35)","v":1.0749999999999996E-2},{"$f":"SUM(C33:C35)","v":1.7000000000000008E-2},{"$f":"SUM(D33:D35)","v":-1.1000000000000001E-2},{"$f":"SUM(E33:E35)","v":-4.9999999999999992E-3},{"$f":"SUM(F33:F35)","v":1.1750000000000003E-2},null,{"$f":"F27-F35","v":1.0249999999999995E-2}]
["Wp (Rp-Rb)",{"$f":"B20*B28","v":-1.7500000000000013E-3},{"$f":"C20*C28","v":4.0000000000000036E-3},{"$f":"D20*D28","v":-3.0000000000000005E-3},{"$f":"E20*E28","v":1.0000000000000009E-3},{"$f":"SUM(B37:E37)","v":2.5000000000000239E-4},"SP* = SP+X"]
["(Wp-Wb) Rb",{"$f":"B34","v":1.2499999999999997E-2},{"$f":"C34","v":1.3000000000000005E-2},{"$f":"D34","v":-8.0000000000000002E-3},{"$f":"E34","v":-6.0000000000000001E-3},{"$f":"F34","v":1.1500000000000002E-2},"AA","allocation"]
[null,{"$f":"SUM(B37:B38)","v":1.0749999999999996E-2},{"$f":"SUM(C37:C38)","v":1.7000000000000008E-2},{"$f":"SUM(D37:D38)","v":-1.1000000000000001E-2},{"$f":"SUM(E37:E38)","v":-4.9999999999999992E-3},{"$f":"SUM(F37:F38)","v":1.1750000000000003E-2}]
["(Rp-Rb-bs+rs)/2",{"$f":"(B31-B26+B25-B30)/2","v":-1.5000000000000013E-3},{"$f":"(C31-C26+C25-C30)/2","v":3.5000000000000031E-3},{"$f":"(D31-D26+D25-D30)/2","v":-4.000000000000001E-3},{"$f":"(E31-E26+E25-E30)/2","v":1.5000000000000013E-3},{"$f":"(F31-F26+F25-F30)/2","v":-5.0000000000000044E-4},"SP'",{"$f":"form(F40)","v":{"$error":"#NAME?"}}]
["(Rp-Rb+bs-rs)/2",{"$f":"(B30-B26+B25-B31)/2","v":1.2249999999999997E-2},{"$f":"(C30-C26+C25-C31)/2","v":1.3500000000000002E-2},{"$f":"(D30-D26+D25-D31)/2","v":-7.0000000000000001E-3},{"$f":"(E30-E26+E25-E31)/2","v":-6.5000000000000006E-3},{"$f":"(F30-F26+F25-F31)/2","v":1.2249999999999997E-2},"AA'",{"$error":"#NAME?"}]
[null,{"$f":"SUM(B40:B41)","v":1.0749999999999996E-2},{"$f":"SUM(C40:C41)","v":1.7000000000000005E-2},{"$f":"SUM(D40:D41)","v":-1.1000000000000001E-2},{"$f":"SUM(E40:E41)","v":-4.9999999999999992E-3},{"$f":"SUM(F40:F41)","v":1.1749999999999997E-2}]
["(Rp-Rb)/2-(bs-rs)/2",{"$f":"(B27-B32)/2","v":-1.5000000000000013E-3},{"$f":"(C27-C32)/2","v":3.5000000000000031E-3},-4.000000000000001E-3,1.5000000000000013E-3,-5.0000000000000044E-4,"SP'",{"$f":"form(F43)","v":{"$error":"#NAME?"}}]
["(Rp-Rb)/2+(bs-rs)/2",{"$f":"(B27+B32)/2","v":1.2249999999999997E-2},{"$f":"(C27+C32)/2","v":1.3500000000000005E-2},-7.0000000000000001E-3,-6.5000000000000006E-3,1.2249999999999997E-2,"AA'",{"$error":"#NAME?"}]
[null,{"$f":"SUM(B43:B44)","v":1.0749999999999996E-2},{"$f":"SUM(C43:C44)","v":1.7000000000000008E-2},{"$f":"SUM(D43:D44)","v":-1.1000000000000001E-2},{"$f":"SUM(E43:E44)","v":-4.9999999999999992E-3},{"$f":"SUM(F43:F44)","v":1.1749999999999997E-2}]
["méthode géométrique"]
["1+Rp",{"$f":"1+B22","v":1.1200000000000001},1.1400000000000001,1.06,1.07,{"$f":"F25","v":0.114}]
["1+Rb",1.125,1.1299999999999999,1.08,1.06,{"$f":"F26","v":0.10225000000000001}]
[null,null,null,null,null,{"$f":"(1+F47)/(1+F48)-1","v":1.0660013608528196E-2}]
["Rp-Rb",{"$f":"B47/B48-1","v":-4.444444444444362E-3},{"$f":"C47/C48-1","v":8.8495575221241296E-3},{"$f":"D47/D48-1","v":-1.851851851851849E-2},{"$f":"E47/E48-1","v":9.4339622641510523E-3},{"$f":"SUM(B50:E50)","v":-4.6794431766876698E-3}]
["Rb-b",{"$f":"B48/(1+$F$48)","v":1.0206396008165117},{"$f":"C48/(1+$F$48)","v":1.0251757768201406},{"$f":"D48/(1+$F$48)","v":0.97981401678385127},{"$f":"E48/(1+$F$48)","v":0.96166931276933554},{"$f":"SUM(B51:E51)","v":3.9872987071898391}]
[]
["AA",{"$f":"B29*(B51-1)","v":2.0639600816511725E-3},{"$f":"C29*(C51-1)","v":2.5175776820140616E-3},{"$f":"D29*(D51-1)","v":2.0185983216148731E-3},{"$f":"E29*(E51-1)","v":3.8330687230664464E-3},{"$f":"SUM(B53:E53)","v":1.0433204808346554E-2}]
["SP",{"$f":"B20*B50*B51","v":-1.5876616012700999E-3},{"$f":"C20*C50*C51","v":3.6289408029032493E-3},{"$f":"D20*D50*D51","v":-2.7217056021773605E-3},{"$f":"E20*E50*E51","v":9.0723520072579877E-4},{"$f":"SUM(B54:E54)","v":2.2680880018158766E-4}]
[null,null,null,null,null,{"$f":"F53+F54","v":1.0660013608528142E-2}]
[]
[null,"Attribution de Performance"]
[]
[null,null,"UK","JAPAN","US","TOTAL"]
[null,"Wp",0.4,0.3,0.3,1]
[null,"Wb",0.4,0.2,0.4,1]
[null,"Rp",0.2,-0.05,0.06]
[null,"Rb",0.1,-0.04,0.08]
[]
["Portfolio",null,{"$f":"C62*C60","v":8.0000000000000016E-2},-1.4999999999999999E-2,1.7999999999999999E-2,{"$f":"SUMPRODUCT(B60:E60,B62:E62)","v":8.3000000000000018E-2},"Rp"]
["Benchmark",null,4.0000000000000008E-2,-8.0000000000000002E-3,3.2000000000000001E-2,{"$f":"SUMPRODUCT(B61:E61,B63:E63)","v":6.4000000000000001E-2},"Rb"]
[null,null,null,null,null,{"$f":"F65-F66","v":1.9000000000000017E-2},null,{"$f":"IF(F67>0,\"sur-performance\",\"sous-performance\")","v":"sur-performance"}]
["Rp-Rb",null,{"$f":"C62-C63","v":0.1},{"$f":"D62-D63","v":-1.0000000000000002E-2},{"$f":"E62-E63","v":-2.0000000000000004E-2}]
["Wp-Wb",null,{"$f":"C60-C61","v":0},{"$f":"D60-D61","v":9.9999999999999978E-2},{"$f":"E60-E61","v":-0.10000000000000003}]
["bs",null,{"$f":"C60*C63","v":4.0000000000000008E-2},{"$f":"D60*D63","v":-1.2E-2},{"$f":"E60*E63","v":2.4E-2},{"$f":"SUMPRODUCT(B60:E60,B63:E63)","v":5.2000000000000005E-2},"bs"]
["rs",null,{"$f":"C61*C62","v":8.0000000000000016E-2},{"$f":"D61*D62","v":-1.0000000000000002E-2},{"$f":"E61*E62","v":2.4E-2},{"$f":"SUMPRODUCT(B61:E61,B62:E62)","v":9.4E-2},"rs"]
["méthode arithmétique"]
["Wb (Rp-Rb)",null,{"$f":"C68*C61","v":4.0000000000000008E-2},{"$f":"D68*D61","v":-2.0000000000000005E-3},{"$f":"E68*E61","v":-8.0000000000000019E-3},{"$f":"SUM(B73:E73)","v":3.0000000000000006E-2},"SP","sélection"]
["(Wp-Wb) Rb",null,{"$f":"C69*C63","v":0},{"$f":"D69*D63","v":-3.9999999999999992E-3},{"$f":"E69*E63","v":-8.0000000000000036E-3},{"$f":"SUM(B74:E74)","v":-1.2000000000000004E-2},"AA","allocation"]
["(Wp-Wb) (Rp-Rb)",null,{"$f":"C68*C69","v":0},{"$f":"D68*D69","v":-1E-3},{"$f":"E68*E69","v":2.0000000000000009E-3},{"$f":"SUM(B75:E75)","v":1.0000000000000009E-3},"X","interaction"]
[null,null,{"$f":"SUM(C73:C75)","v":4.0000000000000008E-2},{"$f":"SUM(D73:D75)","v":-7.0000000000000001E-3},{"$f":"SUM(E73:E75)","v":-1.4000000000000005E-2},{"$f":"SUM(F73:F75)","v":1.9000000000000003E-2},null,{"$f":"F67-F75","v":1.8000000000000016E-2}]
[null,null,{"$f":"C60*C68","v":4.0000000000000008E-2},{"$f":"D60*D68","v":-3.0000000000000005E-3},{"$f":"E60*E68","v":-6.000000000000001E-3},{"$f":"SUM(B77:E77)","v":3.1000000000000003E-2},"SP* = SP+X"]
[null,null,{"$f":"C74","v":0},{"$f":"D74","v":-3.9999999999999992E-3},{"$f":"E74","v":-8.0000000000000036E-3},{"$f":"F74","v":-1.2000000000000004E-2},"AA","allocation"]
[null,null,{"$f":"SUM(C77:C78)","v":4.0000000000000008E-2},{"$f":"SUM(D77:D78)","v":-6.9999999999999993E-3},{"$f":"SUM(E77:E78)","v":-1.4000000000000005E-2},{"$f":"SUM(F77:F78)","v":1.9E-2}]
[null,null,{"$f":"(C71-C66+C65-C70)/2","v":4.0000000000000008E-2},{"$f":"(D71-D66+D65-D70)/2","v":-2.5000000000000005E-3},{"$f":"(E71-E66+E65-E70)/2","v":-7.000000000000001E-3},{"$f":"(F71-F66+F65-F70)/2","v":3.0500000000000006E-2},"SP'"]
[null,null,{"$f":"(C70-C66+C65-C71)/2","v":0},{"$f":"(D70-D66+D65-D71)/2","v":-4.4999999999999988E-3},{"$f":"(E70-E66+E65-E71)/2","v":-7.000000000000001E-3},{"$f":"(F70-F66+F65-F71)/2","v":-1.1499999999999989E-2},"AA'"]
[null,null,{"$f":"SUM(C80:C81)","v":4.0000000000000008E-2},{"$f":"SUM(D80:D81)","v":-6.9999999999999993E-3},{"$f":"SUM(E80:E81)","v":-1.4000000000000002E-2},{"$f":"SUM(F80:F81)","v":1.9000000000000017E-2}]
["méthode géométrique"]
["1+Rp",null,{"$f":"1+C62","v":1.2},0.95,1.06,{"$f":"F65","v":8.3000000000000018E-2}]
["1+Rb",null,1.1000000000000001,0.96,1.08,{"$f":"F66","v":6.4000000000000001E-2}]
[null,null,null,null,null,{"$f":"(1+F84)/(1+F85)-1","v":1.7857142857142794E-2}]
["Rp-Rb",null,{"$f":"C84/C85-1","v":9.0909090909090828E-2},{"$f":"D84/D85-1","v":-1.041666666666663E-2},{"$f":"E84/E85-1","v":-1.851851851851849E-2},{"$f":"SUM(B87:E87)","v":6.1973905723905709E-2}]
["Rb-b",null,{"$f":"C85/(1+$F$48)","v":0.99795872079836712},{"$f":"D85/(1+$F$48)","v":0.87094579269675665},{"$f":"E85/(1+$F$48)","v":0.97981401678385127},{"$f":"SUM(B88:E88)","v":2.8487185302789753}]
[]
["AA",null,{"$f":"C69*(C88-1)","v":0},{"$f":"D69*(D88-1)","v":-1.2905420730324333E-2},{"$f":"E69*(E88-1)","v":2.0185983216148735E-3},{"$f":"SUM(B90:E90)","v":-1.0886822408709459E-2}]
["SP",null,{"$f":"C60*C87*C88","v":3.6289408029031502E-2},{"$f":"D60*D87*D88","v":-2.7217056021773549E-3},{"$f":"E60*E87*E88","v":-5.4434112043547211E-3},{"$f":"SUM(B91:E91)","v":2.8124291222499424E-2}]
[null,null,null,null,null,{"$f":"F90+F91","v":1.7237468813789965E-2}]
[]
[]
["3.04","Attribution d'une surperformance géométrique"]
[]
["Portefeuille"]
[null,"Poids moyen","Poids relat",null,"Perf.","Contrib.",null,null,{"$f":"A97","v":"Portefeuille"},{"$f":"B98","v":"Poids moyen"},{"$f":"E98","v":"Perf."}]
["Actions",420,{"$f":"B99/B101","v":0.42},null,0.2,{"$f":"E99*C99","v":8.4000000000000005E-2},null,null,{"$f":"A99","v":"Actions"},{"$f":"B99","v":420},{"$f":"E99","v":0.2}]
["Taux",580,{"$f":"B100/B101","v":0.57999999999999996},null,0.02,{"$f":"E100*C100","v":1.1599999999999999E-2},null,null,{"$f":"A100","v":"Taux"},{"$f":"B100","v":580},{"$f":"E100","v":0.02}]
["Total",{"$f":"B99+B100","v":1000},{"$f":"C99+C100","v":1},null,null,{"$f":"F99+F100","v":9.5600000000000004E-2},null,null,{"$f":"A101","v":"Total"},{"$f":"B101","v":1000}]
[]
["Benchmark"]
[null,"Poids moyen","Poids relat",null,"Perf.","Contrib.",null,null,{"$f":"A103","v":"Benchmark"},{"$f":"B104","v":"Poids moyen"},{"$f":"E104","v":"Perf."}]
["Actions",500,{"$f":"B105/B107","v":0.41666666666666669},null,0.15,{"$f":"C105*E105","v":6.25E-2},null,null,{"$f":"A105","v":"Actions"},{"$f":"B105","v":500},{"$f":"E105","v":0.15}]
["Taux",700,{"$f":"B106/B107","v":0.58333333333333337},null,0.01,{"$f":"C106*E106","v":5.8333333333333336E-3},null,null,{"$f":"A106","v":"Taux"},{"$f":"B106","v":700},{"$f":"E106","v":0.01}]
["Ensemble",{"$f":"B105+B106","v":1200},{"$f":"C105+C106","v":1},null,null,{"$f":"F105+F106","v":6.8333333333333329E-2},null,null,{"$f":"A107","v":"Ensemble"},{"$f":"B107","v":1200}]
[]
[]
[null,"Portefeuille",null,null,"Benchmark"]
[null,"Poids","Perf",null,"Poids","Perf"]
["Actions",{"$f":"C99","v":0.42},{"$f":"E99","v":0.2},null,{"$f":"C105","v":0.41666666666666669},{"$f":"E105","v":0.15}]
["Taux",{"$f":"C100","v":0.57999999999999996},{"$f":"E100","v":0.02},null,{"$f":"C106","v":0.58333333333333337},{"$f":"E106","v":0.01}]
["Total",{"$f":"C101","v":1},{"$f":"F101","v":9.5600000000000004E-2},null,1,{"$f":"F107","v":6.8333333333333329E-2}]
[]
[null,"Constats",null,"Attribution de l'alpha"]
[null,"Pari actif","Ecart perf","Allocation",null,"Selection"]
["Actions",-0.10253521126760562,{"$f":"(1+C112)/(1+F112)-1","v":4.3478260869565188E-2},{"$f":"(B112-E112)*(F112-$F$114)/(1+$F$114)","v":2.5481019240769368E-4},null,{"$f":"B112*(C112-F112)/(1+$F$114)","v":1.9656786271450861E-2}]
["Taux",0.10253521126760567,{"$f":"(1+C113)/(1+F113)-1","v":9.9009900990099098E-3},{"$f":"(B113-E113)*(F113-$F$114)/(1+$F$114)","v":1.8200728029121581E-4},null,{"$f":"B113*(C113-F113)/(1+$F$114)","v":5.4290171606864272E-3}]
["Total",0,{"$f":"(1+C114)/(1+F114)-1","v":2.5522620904836035E-2},{"$f":"D119+D118","v":4.3681747269890946E-4},{"$f":"E119+E118","v":0},{"$f":"F119+F118","v":2.5085803432137288E-2}]

{"sheet":"AtributionPerf","index":11,"n_rows":77,"n_cols":10,"rows_kept":77,"cut":false,"hidden":false,"merged":[]}
[]
[]
[null,null,"UK","JAPAN","US","TOTAL"]
[null,"Wpi",0.4,0.3,0.3,1]
[null,"Wbi",0.4,0.2,0.4,1]
[null,"Rpi",20,-5,6,null,{"$f":"SUMPRODUCT(C4:E4,C6:E6)","v":8.3000000000000007},"Rp"]
[null,"Rbi",10,-4,8,null,{"$f":"SUMPRODUCT(C5:E5,C7:E7)","v":6.4},"Rb"]
[null,"Rbi-Rb",{"$f":"C7-$G$7","v":3.5999999999999996},{"$f":"D7-$G$7","v":-10.4},1.5999999999999996,null,{"$f":"G6-G7","v":1.9000000000000004}]
[null,"Wp-Wb",{"$f":"C4-C5","v":0},{"$f":"D4-D5","v":9.9999999999999978E-2},-0.10000000000000003]
[null,"Rp-Rb",{"$f":"C6-C7","v":10},{"$f":"D6-D7","v":-1},-2]
[]
[]
["ASSET ALLOCATION","(Wp-Wb).Rbi",{"$f":"C9*C7","v":0},{"$f":"D9*D7","v":-0.39999999999999991},{"$f":"E9*E7","v":-0.80000000000000027},{"$f":"SUM(C13:E13)","v":-1.2000000000000002}]
["ASSET ALLOCATION","(Wp-Wb).(Rbi-Rb)",{"$f":"C9*C8","v":0},{"$f":"D9*D8","v":-1.0399999999999998},{"$f":"E9*E8","v":-0.16000000000000003},{"$f":"SUM(C14:E14)","v":-1.1999999999999997}]
["SELECTION","Wbi.(Rp-Rb)",{"$f":"C10*C5","v":4},{"$f":"D10*D5","v":-0.2},{"$f":"E10*E5","v":-0.8},{"$f":"SUM(C15:E15)","v":3}]
["INTERACTIVITE","(Wp-Wb)(Rp-Rb)",{"$f":"C9*C10","v":0},{"$f":"D9*D10","v":-9.9999999999999978E-2},0.20000000000000007,{"$f":"SUM(C16:E16)","v":0.10000000000000009}]
[null,null,{"$f":"SUM(C14:C16)","v":4},{"$f":"SUM(D14:D16)","v":-1.3399999999999999},-0.76,{"$f":"SUM(F14:F16)","v":1.9000000000000004}]
[]
[]
[null,null,"UK","JAPAN","US","TOTAL"]
[null,"Wpi",0.4,0.4,0.2,1]
[null,"Wbi",0.35,0.5,0.15,1]
[null,"Rpi",13,6.75,11,null,{"$f":"SUMPRODUCT(C21:E21,C23:E23)","v":10.100000000000001},"Rp"]
[null,"Rbi",12,7,11,null,{"$f":"SUMPRODUCT(C22:E22,C24:E24)","v":9.35},"Rb"]
[null,"Rbi-Rb",{"$f":"C24-$G$24","v":2.6500000000000004},{"$f":"D24-$G$24","v":-2.3499999999999996},1.6500000000000004,null,{"$f":"G23-G24","v":0.75000000000000178}]
[null,"Wp-Wb",{"$f":"C21-C22","v":5.0000000000000044E-2},{"$f":"D21-D22","v":-9.9999999999999978E-2},5.0000000000000017E-2]
[null,"Rp-Rb",{"$f":"C23-C24","v":1},{"$f":"D23-D24","v":-0.25},0]
[]
[]
["ASSET ALLOCATION","(Wp-Wb).Rbi",{"$f":"C26*C24","v":0.60000000000000053},{"$f":"D26*D24","v":-0.69999999999999984},{"$f":"E26*E24","v":0.55000000000000016},{"$f":"SUM(C30:E30)","v":0.45000000000000084}]
["ASSET ALLOCATION","(Wp-Wb).(Rbi-Rb)",{"$f":"C26*C25","v":0.13250000000000015},{"$f":"D26*D25","v":0.2349999999999999},{"$f":"E26*E25","v":8.2500000000000046E-2},{"$f":"SUM(C31:E31)","v":0.45000000000000007}]
["SELECTION","Wbi.(Rp-Rb)",{"$f":"C27*C22","v":0.35},{"$f":"D27*D22","v":-0.125},{"$f":"E27*E22","v":0},{"$f":"SUM(C32:E32)","v":0.22499999999999998}]
["INTERACTIVITE","(Wp-Wb)(Rp-Rb)",{"$f":"C26*C27","v":5.0000000000000044E-2},{"$f":"D26*D27","v":2.4999999999999994E-2},0,{"$f":"SUM(C33:E33)","v":7.5000000000000039E-2}]
[null,null,{"$f":"SUM(C31:C33)","v":0.5325000000000002},{"$f":"SUM(D31:D33)","v":0.1349999999999999},8.2500000000000046E-2,{"$f":"SUM(F31:F33)","v":0.75000000000000011}]
[]
[null,null,"UK","JAPAN",null,"TOTAL"]
[null,"Wpi",0.25,0.75,null,1]
[null,"Wbi",0.2,0.8,null,1]
[null,"Rpi",-5.13,7.24,null,null,{"$f":"SUMPRODUCT(C37:E37,C39:E39)","v":4.1475},"Rp"]
[null,"Rbi",4.9000000000000004,-10.56,null,null,{"$f":"SUMPRODUCT(C38:E38,C40:E40)","v":-7.468},"Rb"]
[null,"Rbi-Rb",{"$f":"C40-G40","v":12.368},{"$f":"D40-G40","v":-3.0920000000000005},null,null,{"$f":"G39-G40","v":11.615500000000001}]
[null,"Wp-Wb",{"$f":"C37-C38","v":4.9999999999999989E-2},{"$f":"D37-D38","v":-5.0000000000000044E-2}]
[null,"Rp-Rb",{"$f":"C39-C40","v":-10.030000000000001},{"$f":"D39-D40","v":17.8}]
[]
[]
["ASSET ALLOCATION","(Wp-Wb).Rbi",{"$f":"C42*C40","v":0.24499999999999997},{"$f":"D42*D40","v":0.52800000000000047},null,{"$f":"SUM(C46:E46)","v":0.77300000000000046}]
["ASSET ALLOCATION","(Wp-Wb).(Rbi-Rb)",{"$f":"C42*C41","v":0.61839999999999984},{"$f":"D42*D41","v":0.15460000000000015},null,{"$f":"SUM(C47:E47)","v":0.77300000000000002}]
["SELECTION TITRE","Wbi.(Rp-Rb)",{"$f":"C43*C38","v":-2.0060000000000002},{"$f":"D43*D38","v":14.240000000000002},null,{"$f":"SUM(C48:E48)","v":12.234000000000002}]
["INTERACTIVITE","(Wp-Wb)(Rp-Rb)",{"$f":"C42*C43","v":-0.50149999999999995},{"$f":"D42*D43","v":-0.89000000000000079},null,{"$f":"SUM(C49:E49)","v":-1.3915000000000006}]
[null,null,{"$f":"SUM(C47:C49)","v":-1.8891000000000004},{"$f":"SUM(D47:D49)","v":13.504600000000002},null,{"$f":"SUM(F47:F49)","v":11.615500000000001}]
[]
[null,null,"UK","JAPAN","US","TOTAL"]
[null,"Wpi",0.3,0.4,0.3,1]
[null,"Wbi",0.5,0.25,0.25,1]
[null,"Rpi",15.75,21.75,28.25,null,{"$f":"SUMPRODUCT(C53:E53,C55:E55)","v":21.9},"Rp",0.11250000000000002,0.1575]
[null,"Rbi",16.25,20,28,null,{"$f":"SUMPRODUCT(C54:E54,C56:E56)","v":20.125},"Rb",0.2175,0.2175]
[null,"Rbi-Rb",{"$f":"C56-$G$56","v":-3.875},{"$f":"D56-$G$56","v":-0.125},7.875,null,{"$f":"G55-G56","v":1.7749999999999986},null,0.215,0.28250000000000003]
[null,"Wp-Wb",{"$f":"C53-C54","v":-0.2},{"$f":"D53-D54","v":0.15000000000000002},4.9999999999999989E-2,null,null,null,null,"Ri"]
[null,"Rp-Rb",{"$f":"C55-C56","v":-0.5},{"$f":"D55-D56","v":1.75},0.25,null,null,null,0.16250000000000001]
[null,null,null,null,null,null,null,null,0.155,"Rend My(Rib) "]
[null,null,null,null,null,null,null,null,0.22999999999999998,0.16250000000000001]
["ASSET ALLOCATION","(Wp-Wb).Rbi",{"$f":"C58*C56","v":-3.25},{"$f":"D58*D56","v":3.0000000000000004},{"$f":"E58*E56","v":1.3999999999999997},{"$f":"SUM(C62:E62)","v":1.1500000000000001},null,null,null,0.2]
["ASSET ALLOCATION","(Wp-Wb).(Rbi-Rb)",{"$f":"C58*C57","v":0.77500000000000002},{"$f":"D58*D57","v":-1.8750000000000003E-2},{"$f":"E58*E57","v":0.39374999999999993},{"$f":"SUM(C63:E63)","v":1.1499999999999999},null,null,null,0.28000000000000003]
["SELECTION","Wbi.(Rp-Rb)",{"$f":"C59*C54","v":-0.25},{"$f":"D59*D54","v":0.4375},{"$f":"E59*E54","v":6.25E-2},{"$f":"SUM(C64:E64)","v":0.25}]
["INTERACTIVITE","(Wp-Wb)(Rp-Rb)",{"$f":"C58*C59","v":0.1},{"$f":"D58*D59","v":0.26250000000000007},1.2499999999999997E-2,{"$f":"SUM(C65:E65)","v":0.37500000000000006}]
[null,null,{"$f":"SUM(C63:C65)","v":0.625},{"$f":"SUM(D63:D65)","v":0.68125000000000013},0.46874999999999994,{"$f":"SUM(F63:F65)","v":1.7749999999999999}]
[null,1,{"$f":"B67-$B$72","v":-2},{"$f":"C67^4","v":16},{"$f":"C67*C67","v":4}]
[null,2,{"$f":"B68-$B$72","v":-1},{"$f":"C68^4","v":1},{"$f":"C68*C68","v":1}]
[null,3,0,0,0]
[null,4,1,1,1]
[null,5,2,16,4]
[null,{"$f":"AVERAGE(B67:B71)","v":3},null,{"$f":"SUM(D67:D71)","v":34},{"$f":"SUM(E67:E71)","v":10}]
[null,null,null,null,{"$f":"STDEV(B67:B71)","v":1.5811388300841898},{"$f":"STDEVP(B67:B71)","v":1.4142135623730951},{"$f":"E73/F73","v":1.1180339887498949}]
[null,null,null,{"$f":"D72/E74/E74","v":5.4399999999999986},{"$f":"E73*E73","v":2.5000000000000004},{"$f":"F73*F73","v":2.0000000000000004},{"$f":"E74/F74","v":1.25}]
[null,{"$f":"KURT(B67:B71)","v":-1.1999999999999984},null,{"$f":"5*6/4/3/2","v":1.25}]
[null,null,null,{"$f":"3*4*4/3/2","v":8}]
[null,null,null,{"$f":"D74*D75-D76","v":-1.200000000000002}]